How to fix RegCreateKeyEx() returned error 5, 'Access is denied.' in SQL Server 2017

Question

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?

Answer 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.