Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.
Solved
Unable to change SQL 2008 from Windows Authentication to Windows/SQL mixed mode
Posted on 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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
2- +1
10 Comments
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/
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/
Active 5 days ago
Do you have local administrator access on the server? Also, are you a member of the sysadmin fixed server role?
Active 5 days ago
Sorry lcohan - I was had a couple of windows open and didn't realize you had already posted on this one.
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.
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.
"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.
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.
Active today
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\SOFTWAR
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/
run –> regedit.exe->
HKEY_LOCAL_MACHINE\SOFTWAR
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/
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
Thank you so much lcohan!
SQL-sa-error.png
Featured Post
Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Course of the Month6 days, 16 hours left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.