snippetsqlMinor
How to change SQL sever configuration manager settings using TSQL?
Viewed 0 times
tsqlsettingssqlseverusinghowmanagerconfigurationchange
Problem
SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc...
Is it possible to make these changes that is done in SQL server configuration manager by using TSQL statements, or in the SSMS?
Is it possible to make these changes that is done in SQL server configuration manager by using TSQL statements, or in the SSMS?
Solution
Most settings that can't be done via sp_configure are registry based
So, you can use
One examples , protocols is under
and each of these has an
One important note: you can stop SQL Server from xp_cmdshell or using SHUTDOWN but not start it of course...
So, you can use
xp_instance_regread etc to change them. You'll have to find a list of registry keys yourself sorry but most are under HKLM\Software\Microsoft\Microsoft SQL ServerOne examples , protocols is under
...
...(instancename)\MSSQLServer\SuperSocketNetLib\Tcp
...(instancename)\MSSQLServer\SuperSocketNetLib\Sm
...and each of these has an
Enabled value One important note: you can stop SQL Server from xp_cmdshell or using SHUTDOWN but not start it of course...
Code Snippets
...
...(instancename)\MSSQLServer\SuperSocketNetLib\Tcp
...(instancename)\MSSQLServer\SuperSocketNetLib\Sm
...Context
StackExchange Database Administrators Q#3056, answer score: 5
Revisions (0)
No revisions yet.