How to change the SQL Server 2008 Login authentication method

Configure how SQL Server is authenticated

By default, SQL Server 2005 Express is integrated with Windows Security authentication and the sa login is disabled. For a workgroup environment that does not use inconvenient Windows Integrated Security authentication, we want to enable hybrid security authentication for SQL Server 2005 Express, that is, SQL Server to authenticate users instead of Windows to authenticate users.

1. Use SQL Server Management Studio Express.

When we start SQL Server Management Studio Express, first it wants to connect to our SQL Server 2005 Express, when we install SQL Server 2005 Express, the default instance is sqlexpr ESS, the server name consists of the machine name \ Instance name, therefore, the service name for this example is w2k3-c\sqlexpress (note: The machine named W2k3-c for SQL Server 2005 Express is installed). The first time you use SQL Server Management Studio Express, because we have to adopt Windows authentication, this is the default installation decision.

A) Set the authentication method for SQL Server 2005 Express

b) Set the SA password and enable the SA login name

Since we do not know the sa password, we must set one! Do not close this window, then select "Status" on the left, by default, the sa login is disabled, so we must enable the sa login, select "Enable" under Login on the right

Finally, we click on the "OK" button below the window. OK, so far, the SQL Server 2005 Express Server has been able to have SA logged in, but to reboot, let the configuration take effect.

c) Verifying the sa login

We create a new text file on the desktop, the file name is Testsql.udl, double-click the file to open the Data Link Properties dialog box, click on the "Provider" tab, such as:

Click the "Next" button to enter the "Advanced" tab and enter the data source as: W2k3-c\sqlexpress, which is the SQL Server 2005 Express instance that we installed, and select "Use specified user name and password", enter the password for the login SA and SA, and finally, We test the sa login by clicking the "Test Connection" button. We were pleasantly surprised to see that the connection was successful! For the next convenience, tick "Allow save password" and finally, click "OK" button to save this data link.