Resolving Can't Login To Sqlexpress Using Sa Account When you install VS 2005 or VS 2008 it will eventually install an inst

[innosia 1]

When installing Visual Studio 2005 or Visual Studio 2008, the installer will automatically install for us one instance of SQL Server 2005 Express. But we can't actually connect the database without extra tools and we can't login except using Integrated Security. In this article I will explain how to download extra tools and how to login using sa account (not Windows Authentication Mode).

1. First download Management Studio Express for SQL Server Express 2005, http://www.microsoft.com/en-us/download this tool will enable you to connect with SQLExpress instance that is installed via Visual Studio. Install this tool and you can connect to database [MyComputerName]\SQLEXPRESS but for first you will need to use Windows Authentication mode. This way you can connect to the instance.

2. After success connecting the database, Open Security tree, Logins tree and you can see sa account. Double click 'sa' account and from General page, change the Password and Confirm Password to new password that you want. Then change to Status page, on Login section select Enabled. Then click OK.

3. Now right click the [MyComputerName]/SQLExpress and select Properties then open the Security page, on Server Authentication section select SQL Server and Windows Authentication Mode.


4. Open Services diaog box, (on Control Panel open Administrative Tools, then Services). Select the SQL Server (SQLEXPRESS) service, restart this service.

5. Now you can connect to SQLEXPRESS instance using Management Studio via sa account and new password you set.

This solves many issues by developer that often ask me or asking on the net
1. Why can't I connect to SQLExpress instance using sa account?
2. Why can't I connect to SQLExpress using a username and password that is assigned to me by database administrator?
3. How to configure SQLExpress to accept user login from SQL Server authentication?

The issues raise because by default Microsoft has (once again) made the free SQLEXPRESS by default instance only with Windows Authentication Mode (which means that it will accept connection only from integrated security, like from local computer or computer that has login for it). And by that reason, SQLEXPRESS is limited to developer, thus some developer does not know that this default behaviour can actually reconfigure. After configuration you may see that SQLEXPRESS can now act like a decent database, and even more if you read my other article here you can even configure the instance name SQLEXPRESS to whatever you like!

And Microsoft has released the free Management Studio Express that works with SQLEXPRESS 2005, then you might find that your default free database can actually connected by an application with user interface (not the old MSDE). What I wonder is why Microsoft not by default installing Management Studio and ship it with Visual Studio 2005 and Visual Studio 2008??? Hehe..

[iGuest 3]

SQL Express Remote ConnectionResolving Can't Login To Sqlexpress Using Sa Account

I have microsoft sql express installed on one computer running on windows authentication.I am trying to access the tables in one of the databases on it from another computer.When I enter the server ip and use windows authentication, it logs in successfully. And I am able to view the databases on the other pc. However I am only able to view system tables. And not the ones I want (which are user tables).can anyone help? I would be grateful. I have been stuck on this for days and I've searched every possible site on the internet. Thanks in advance!

-question by cute

[iGuest 3]

RE: Resolving CanResolving Can't Login To Sqlexpress Using Sa Account

I have installed microsoft sqlexpress on PC. I want to login into sql using sa account on the same computer using VB.NET. I have Visual Studio 2005. My syntax is correct and everything is fine but I m not able to make a connection between VB.NET and sql. So please put some light on my issue. VB.NET program is as follows:

Imports System.Data.SqlClientPublic Class Form1   Dim con As SqlConnection   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click   con = New SqlConnection("server=localhost;uid=sa;pwd=aman;database=master")   con.Open()   Dim str As String   str = "select * from stdnt"   Dim the As New SqlDataAdapter(str, con)   Dim ds As New DataSet   the.Fill(ds)   DataGridView1.DataSource = ds.Tables(0)   con.Close()   End Sub   Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick   End SubEnd Class

-reply by Aman

[iGuest 3]

Connecting to SQL from another pc...Resolving Can't Login To Sqlexpress Using Sa Account

There may two condition that you must check;

1. Windows firewall; first you shoul start sql browser as service and add as trusted application to windows firewall.  second add sql port for permission to this port.

2. Open sql server both local and remote connection (using Management Studio Express).

Judasis

-reply by Judasis

[iGuest 3]

Thank you for this post! This helped me connect to my database from another program which needed access. Very informative. Thank you again.

[iGuest 3]

Hi! I'm having some problem trying to change the "sa" login information, I'm loged with the Windows Authentication Mode, and when I try to modify the "sa" password, returns with an "Error trying to change password to "sa" ". I'm using Windows 7, and I thing the error is because my Windows Authentification User can't execute the stored procedure to change the password, but I don't know how to resolve this... can anyone help me?Thanks in advance...Al.