Changing SQL Server settings programmatically

Question

Hello friends,

I request you to read my question carefully.

You might know when you install VS2005/2008 with SQL Server Express edition, the SQL Server operates in Windows authentication mode by default. You can use the SQL Server Management Studio to change the mode to Mixed mode (Windows and SQL Server Authentication mode).

Similarly to allow the SQL Server remote connection through TCP/IP, you need to use SQL Server Configuration Manager then select Protocol for SQLEXPRESS and then change the setting for Tcp/IP option.

What i need is to automate this process programmatically using C#. That is, i need to write a c# program to change the mode or change the tcp/ip settings etc.

Can anyone provide me help on this, how could i do that?

Thank you for sharing your valuable time.

Answer 1

You should use SQL Server Management Objects (SMO) - this is an API for managing SQL Server programmatically.

UPDATE:

Proves to be a bit tricky: Server.LoginMode (read/write), Server.TcpEnabled and Server.NamedPipesEnabled (get only, unfortunately). In order to modify protocols, you need to examine Microsoft.SqlServer.Management.Smo.Wmi namespace (hence going from 'the other end'):

• ServerProtocol - represents server protocol
• ServerProtocolCollection - a collection of all protocols defined on a given server

Answer 2

I think you could solve your problem making a silent installation of SQL Server Express edition using a configuration file for the install process.

In this link you can find the command line parameters for the installation.

In this one you can find how to make your configuration file.

Answer 3

What about modifying the registry?

Client Protocol Settings are stored here: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0 Check out ProtocolOrder.

Authentication Mode is stored here: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode

See: Authentication Settings