SQL SERVER – How to Change Authentication Mode Using T-SQL Query

SQL
3 Comments

One of my clients asked an interesting question. It took a while for me to figure out the answer so sharing with my blog readers. In this blog, we would learn how to change authentication mode of SQL Server using T-SQL query instead of SQL Server Management Studio.

THE EMAIL WITH SITUATION

Hi Pinal,

I am an independent vendor of a software which uses SQL Express. I install SQL Server Express on client’s environment as a part of my software deployment. I realized that during installation I missed changing authentication mode to mixed mode and it installs SQL with Windows Authentication mode.

The challenge now is that the software is already shipped with this bug and I need to fix it quickly. There are few downloads so far, so I need to contact my clients and supply a fix. I know how to install SQL with mixed mode authentication, so I have already fixed it for further installations. I cannot ask my client to install SSMS on the machines to change the mode and then uninstall it.

Solarwinds

Do you have any solution for me? I am not well-versed with SQL so there might be an easy answer, but I just do not have time to search on the internet. You can imagine the pressure to get the fix released for already downloads.

Awaiting reply!

WORKAROUND/SOLUTION

As you can see in the above email, he wanted quick help. So, I made an offer, he accepted, and we started working. He told that he knows that below is what needs to be done and restart SQL.

SQL SERVER - How to Change Authentication Mode Using T-SQL Query mixed-auth-tsql-01

Here is the error we would see in ERROLROG when we try to use SQL account to log in and SQL is configured with “Windows Authentication mode”.

Error: 18456, Severity: 14, State: 58.
Login failed for user ‘AppLogin’. Reason: An attempt to login using SQL authentication failed. The server is configured for Windows authentication only. [CLIENT: <local machine>]

Here is the T-SQL to do the same.

  1. To set mode to “Windows Authentication” (LoginMode= 1)
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO
  1. To set mode to “SQL Server and Windows Authentication mode” (LoginMode= 2)
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

I must tell you that my client was smart. He wrote a batch file to change the registry key for the instance which software installed and to restart the SQL Service for that instance. After this software also started working and we both were happy.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Always On AG – HADRAG: Did not Find the Instance to Connect in SqlInstToNodeMap Key
Next Post
SQL SERVER – Azure Key Vault Deletion Error – Vault ‘ProdRecoveryVault’ Cannot be Deleted as There are Existing Resources Within the Vault

Related Posts

3 Comments. Leave new

  • Out of interest, was it necessary to do this from SQL, or could the same outcome be achieved with REGEDIT.EXE or by merging a .REG file, for example?

    Reply
  • Linked server scripts running very slow . Server A to B takes 45 minutes but same script takes 10 minutes server A to C. Same server have the DB both B and C servers,
    I have restored same DB from prod, Server A and C is SQL Server 2008 R2 and
    B SQL Server 2014
    Take time While running /connect from 2008 R2 to 2014,
    Linked server configured, please advise

    Reply

Leave a Reply

Menu