1

I am trying to change auth mode to mix mode with following command in SQL Server 2017:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
                 N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer', 
                 N'LoginMode', REG_DWORD, 2

I have executed this command so far:

EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';
GRANT EXECUTE ON xp_instance_regwrite TO public

After restarting SQL Service I still get this error:

RegCreateKeyEx() returned error 5, 'Access is denied.'

I have given all permissions to all users in SQL, is this something to do with SQL or Windows and how am I able to fix it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ehsan Zargar Ershadi
  • 24,115
  • 17
  • 65
  • 95

2 Answers2

1

For me to work I had do change the user witch executes SQL Background Service. Click on Windows -> search for Services and locate MS SQL Server and right click and change properties , at this place you can change sql user to someone with admin permission. not the best way, but it worked.

Ehsan Zargar Ershadi
  • 24,115
  • 17
  • 65
  • 95
  • I do not know what the "SQL Background Service" is, but changing the SQL Server, the SQL Agent and other SQL Server startup accounts requires the use of SQL Server Configuration Manager. Changing startup accounts via services.msc will not assign required registry permissions. At a later time that use of services.msc can result in access denied to a key. I am not claiming that happened here - I am just adding a "caveat emptor" warning for those who read this post at a later date. – Bill Aug 13 '19 at 15:59
0

This error happens if SQL server cannot change service properties.

You can manually change SQL Server Agent startup type from services as below : enter image description here

Arash.Zandi
  • 1,010
  • 2
  • 13
  • 24