-1

I disabled the windows authentication in SQL server 2014. I don't know how to re-enable it. I tried to google but couldn't find the solution. It says Expand Security and then expand Login. Right-click administrator and click Properties. Click Status and check the box for Login Enabled. first I can't connect as I don't have access anymore. The second solution was through SQL CMD but I also can't open that because the administrator is disabled

  • 2
    I didn't know it was possible to disable Windows authentication for SQL Server. Add to your question how you disabled it. – Dan Guzman Mar 30 '19 at 11:31
  • 3
    I'm voting to close this question as off-topic because you have not indicated what you have tried to resolve this issue yourself. Take a look at my profile for articles on how to ask questions here. One of the tips is to provide some information as to what you've done yourself. [Googling]("google.com/…) gave me lots of stuff! p.s. welcome to the forum, but please try and follow the guidelines! :-) – Vérace Mar 30 '19 at 11:31
4

You didn't disable Windows Authentcation. That's not possible. You may have locked yourself out, but if you are a Windows Administrator, you can restore your access using the procedure documented here, for instance using the following scripts.

If you have a default instance run from an elevated command prompt:

net stop mssqlserver 
net start mssqlserver /mSQLCMD 
sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssqlserver 
net start mssqlserver 
sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

If you have a named instance run from an elevated command prompt, replacing 'sqlexpress' with your instance name if needed:

net stop mssql$sqlexpress 
net start mssql$sqlexpress /mSQLCMD 
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.