-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

closed as off-topic by Vérace, Marcello Miorelli, Erik Darling, John Eisbrener, Tony Hinkle Apr 1 at 20:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Tony Hinkle
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 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 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.'"

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