VB.NET : Change SQL Server Authentication mode

Question

We are using the following code to change the SQL Server authentication from Windows mode to mixed mode.

query.CommandText = "EXEC xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLSERVER', 'LoginMode', REG_DWORD, 2"
query.CommandType = CommandType.Text
query.ExecuteScalar()

During the execution we get the following error.

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

We could change the authentication mode manually. However we need to do this work programmatically.

Help needed.

Take a look at the permissions needed to change the registry key and run the program under an account that has the proper authentication to make the change. — Tim
– Tim, Commented Nov 8, 2014 at 9:23
It might - easiest way to find out is to do it and see what happens. — Tim
– Tim, Commented Nov 8, 2014 at 9:36
Same error running even if the application is run as an administrator. — SHREE
– SHREE, Commented Nov 8, 2014 at 9:43
From MSDN: "Visual Basic .NET offers four functions to access the registry. To use them, you must have Read and Write permission from the RegistryPermissionAccess enumeration. Any code running with full trust (under the default security policy, this is any code installed on the user's local hard drive) has the necessary permissions to access the registry. For more information, see RegistryPermission Class." Do note that this is an older article (VS.NET 2003), but the principles are most likely the same. — Tim
– Tim, Commented Nov 8, 2014 at 9:50

Ben Thul · Accepted Answer · 2014-11-08 15:21:38Z

1

Do this with SMO. I'm going to write it in powershell, but it should be immediately transferable to VB.NET

import-module sqlps -disablenamechecking;

$s = new-object microsoft.sqlserver.management.smo.server '.';
$s.LoginMode = 'mixed';
$s.alter();

This has the advantage of being durable to changes to the location of the registry key across versions (assuming that MS keeps the SMO interface up to date, which they in all likelihood will) and will work with named instances (you'd have to change the registry location in your solution for yours to work).

answered Nov 8, 2014 at 15:21

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VB.NET : Change SQL Server Authentication mode

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related