Changing SQL Server settings programmatically

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 SQ开发者_JS百科L 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.

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

This function in C# will enable TCP/IP Protocol and set the Login mode to Mixed mode.

See complementary information here.

here is the code:

private static bool SetServerProperties()
    {
        #region standardize Connection String
        string tempCatalog = "master";
        string temp = @"Data Source=" + dataSource + ";Initial Catalog=" + tempCatalog + ";Integrated Security=True;MultipleActiveResultSets=True";
        #endregion

        SqlConnection sqlconnection = new SqlConnection(temp);
        SqlCommand cmd = new SqlCommand("select @@ServerName", sqlconnection);
        sqlconnection.Open();
        string serverName = "";
        try
        {
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
                serverName = dr[0].ToString();
        }
        catch
        {
            MessageBox.Show("Failed to Set SQL Server Properties for remote connections.");
        }

        Server srv = new Server(serverName);
        srv.ConnectionContext.Connect();
        srv.Settings.LoginMode = ServerLoginMode.Mixed;

        ManagedComputer mc = new ManagedComputer();

        try
        {
            Service Mysvc = mc.Services["MSSQL$" + serverName.Split('\\')[1]];

            if (Mysvc.ServiceState == ServiceState.Running)
            {
                Mysvc.Stop();
                Mysvc.Alter();

                while (!(string.Format("{0}", Mysvc.ServiceState) == "Stopped"))
                {
                    Mysvc.Refresh();
                }
            }

            ServerProtocol srvprcl = mc.ServerInstances[0].ServerProtocols[2];
            srvprcl.IsEnabled = true;
            srvprcl.Alter();


            Mysvc.Start();
            Mysvc.Alter();

            while (!(string.Format("{0}", Mysvc.ServiceState) == "Running"))
            {
                Mysvc.Refresh();
            }
            return true;
        }
        catch
        {
            MessageBox.Show("TCP/IP connectin could not be enabled.");
            return false;
        }
    }

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

I was able to do this with a small footprint by executing this stored procedure from C#:

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 
GO

It doesn't look like much but works flawlessly and instantly, without restarting services.

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.