change-sql-server-or-sql-server-express-authentication-mode

Change SQL Server or SQL Server Express Authentication Mode

2010-09-02

When I installed SQL Server Express on my local PC, I used to select Windows Authentication mode only, but later I might have to change the Authentication mode for some reasons such as connect this SQL Server database from another computer and I don't want to set a new windows user account for another computer to access current computer, So I have to select SQL Server Authentication mode then.

Actually in SQL Server Express, there are only 2 type of Authentication Modes: Windows Authentication mode and SQL Server and Windows Authentication mode, there is no SQL Server Authentication mode only. So if you want to change from Windows Authentication mode to SQL Server Authentication mode, it will be changed to ****SQL Server and Windows Authentication mode.

Steps:

1: In SQL Server Management Studio Express, right click on the DB Server name, then click Properties:

2: On the Security page, under Server authentication, select the new server authentication mode which you want, and then click OK :

3: In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to** restart SQL Server**.

To restart SQL Server from SQL Server Management Studio: In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL (from Microsoft website)

If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To use the sa login, use the ALTER LOGIN statement to enable the sa login and assign a new password.

Execute the following statements to enable the sa password and assign a password.

      ALTER LOGIN sa ENABLE ;
      GO
      ALTER LOGIN sa WITH PASSWORD = '<enterstrongpasswordhere>' ;
      GO</enterstrongpasswordhere>

To enable the sa login by using Management Studio (from Microsoft website)

In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

On the General page, you might have to create and confirm a password for the sa login.

On the Status page, in the Login section, click Enabled, and then click OK.

Connection String:

Now you can get Connection string:

1: Windows Authentication (Trust Connection):

connectionString="Data Source=MyDBServerAddress\SQLEXPRESS2005;Initial Catalog=S800_DB_2010;Integrated Security=True"

2: SQL Server Authentication:

connectionString="Data Source=MyDBServerAddress\SQLEXPRESS2005;Initial Catalog=testDB;User ID=terminalDBA;Password=myPassword;