SQL Server Windows authentication changed to Mixed Mode authentication

This article transferred from: http://blog.csdn.net/zouyujie1127/article/details/6237849

As needed, change SQL2008 single Windows authentication to mixed-mode authentication. Make a memo here.

Steps:

1. Enter SQL2008 using Windows authentication method, such as:

Click Connect, if an error occurs
First of all, we should think of IS, Sqlserver2008 service does not open, we open the service after reconnecting, problem resolution.

In Object Explorer, right-click the root directory that pops up the server properties.

On the Select page, security, tick SQL Server and Windows Authentication mode, OK. Here you can disconnect from the connection and log in using SQL Server authentication mode.

Because the SA is not enabled by default, if you enable the SA account login, you will also need the following settings:

2. Go back to Object Explorer, expand Security, expand logins to see the login SA, right-click it-Properties, select Page, choose "Status", and click "Enable" on the right. Are you sure.

This allows you to log in with SA and the password is empty by default

3, to repair the sa password, there are two methods, personal recommendation Method 1, simple and clear, the operation is as follows:

Method 1: Direct input

Method 2: Create a new query, execute the statement:

EXEC sp_password NULL, ' Your password ', ' sa ';

(note here is the password policy, if you want to use a simple password, you want to remove the "Enforce password policy" in the SA property page)

Description of sp_password : sp_password oldpassword,newpassword,loginame

However, in subsequent versions of SQL, MS recommends using ALTER login instead of Sp_password:

ALTER login sa with PASSWORD = ' AA '--Change login password to AA

ALTER login is used to change the properties of the SQL Server logon account:

ALTER login Abina with name = abina2020;----Change the login name Abina to abina2020

ALTER LOGIN Abina ENABLE; -----Enable logins that have been disabled

You can now log in with the SA account and the custom password in SQL authentication mode!

4, if you can not log in, do the following attempts:

Open SQL Server Configuration Manager, expand SQL Server Network configuration, MSSQLSERVER protocols, and enable TCP/IP protocol on the right.

Then restart the MSSQLServer service in the SQL Server service.

Some postscript related to this topic:

VS2008 's own database is the Express version of SQL2005, whose default root directory is the system disk:/program Files/microsoft SQL Server/mssql.1/mssql, This path may be different from the SQL2008 path we installed (in fact, in most cases), so in the case of not knowing, when the login server name is selected as Xxx/sqlexpress login, you will be surprised to find that the database you created previously "Lost"! You will know the reason after you have made a relevant understanding.

SQL Server Windows authentication changed to Mixed Mode authentication