Changing SQL Server settings programmatically

Question

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