Solved

Unable to change SQL 2008 from Windows Authentication to Windows/SQL mixed mode

Posted on 2016-10-04
10
43 Views
Last Modified: 2016-10-04
During the troubleshooting of VMware Venter connection to SQL server error, VMware support changed SQL authentication mode from Windows/SQL mixed mode to Windows authentication, but SQL server won't allow us to change it back. See error message attached. I am not able to use sa account to login to SQL on local server anymore. After the VCenter to SQL connection issue was fixed, the Veritas backup job to SQL failed due to the domain admin account I am using on Backup server has insufficient permission (sysadmin) to take SQL snapshot. The backup job was running successful for months when both sa and the domain admin account are all good.

Need a SQL expert's help to let me know how I can switch back SQL authentication from Windows to Windows/SQL mixed mode so I can use sa to login.

Thank you in advance!

Jay
sql-error.png
0
Comment
Question by:aviusa
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 41828741
Is that user AVERUS\aviusadm sysadmin and Local Admin on that OS where SQL is running? Looks like OS registry updates permissions denied is preventing the change.
Also, did you enabled DAC access? if not once you gain back the control of that SQL maybe have a look here and enable the SQL DAC acces:

https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41828762
Do you have local administrator access on the server? Also, are you a member of the sysadmin fixed server role?
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41828764
Sorry lcohan - I was had a couple of windows open and didn't realize you had already posted on this one.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:aviusa
ID: 41828782
lcohan,

Thank you for your post!

averus\aviusadm is domain admin account that I used to login to SQL server locally, it is a member in averus\domain admin group and this group is in SQL local administrator group. It is the Windows authentication account login to SQL too. Try to enable Server Roles - sysadmin in SQL Security -> Login for averus\aviusadm, got error message as well.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 41828795
"Try to enable Server Roles - sysadmin in SQL Security -> Login for averus\aviusadm, got error message as well." - that means is not SQL sysadmin and has limmited access in SQL therefore the "xp_regread" SP errors. That is due to new SQL Security where a Local Admin is no longer implicitly "sysadmin" and you need an account that has "sysadmin" rights in order to switch SQL Authentication mode to mixed.

Try under services and see if SQL Server Service starts with some Windows account and if you have password for that one you can add it to RDP access and local admin, then logout/back into that OS using that SQL startup account and try do the same. If not...we'll have to try find a way to "hack" back into that SQL Server as "sysadmin" if you don't know/have a windows "sysadmin" login.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41828840
try to go do the same from  Registry Editor (need permissions)- make sure login is local admin for simplicity



 run –> regedit.exe->

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer see values

Double click LoginMode   -> 2 (hexidecimal) value for Mixed  mode -- 1 for Windows
--

see more https://www.mssqltips.com/sqlservertip/1441/correct-the-sql-server-authentication-mode-in-the-windows-registry/
0
 

Author Comment

by:aviusa
ID: 41828841
Great, that did the trick! I do have a service account which I used to install the SQL server. Used it to login to SQL and I was able to change the Security back to Windows/SQL mixed mode now. However, I am still not able to use sa to login, SQL prompted an error message (see attached image). I used the same password for that sa account, is it safe to reset it?

Thank you so much lcohan!
SQL-sa-error.png
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 41828852
did you restart server after change?
0
 

Author Comment

by:aviusa
ID: 41828884
It worked! Restarted the server, I am able to use sa to login again. Thank you experts icohan and EugeneZ!

Jay
0
 

Author Closing Comment

by:aviusa
ID: 41828886
It is a great help, thank you lcohan!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question