Dan Dan - 3 months ago 31
SQL Question

Windows Authentication to Native SQL Authentication

I will ask this question, please correct me if i failed to explained it in a right manners.

What i Have --

"WINDOWS" login Authentication to SQL Server 2012


What i Need --

Create a Native SQL Authentication with a User Name and Password


How i can create a SQL Authentication with a User Name and Password !

Any body knows any solution to this problem !

Answer

SQL server has two types of authentication..

1.Windows
2.SQLserver Authentication and windows Auth(mixed)

You can change the authentication to one desired by right clicking on server and clicking on properties as shown in screenshot below..

enter image description here

There are also caveats if only SA account has SYSAdmin permissions..Below is what msdn describes about it..

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. The sa login can only connect to the server by using SQL Server Authentication.

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

Now you can create SQLServer based logins like below..

CREATE LOGIN test
   WITH PASSWORD = 'somepwd'

You also can change authentication using TSQL..

EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    2; --Both windows and SQL

References:
How to change SQL Server authorization mode without Management Studio
https://msdn.microsoft.com/en-IN/library/ms188670.aspx