Changing SQL Server Program Settings

Question

I ask you to carefully read my question.

You may know that when you install VS2005 / 2008 with the release of SQL Server Express, by default SQL Server runs in Windows authentication mode. You can use SQL Server Management Studio to change the mode in mixed mode (Windows and SQL Server authentication mode).

Similar to allowing SQL Server to connect remotely over TCP / IP, you must use SQL Server Configuration Manager, then select the protocol for SQLEXPRESS, and then change the setting for the Tcp / IP parameter.

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

Can someone help me on this, how can I do this?

Thank you for sharing your valuable time.

Answer 1

You must use SQL Server Management Objects ( SMOs ) - this is the API for SQL Server programming.

UPDATE:

It proves a bit more complicated: Server.LoginMode (read / write), Server.TcpEnabled and Server.NamedPipesEnabled (only get, unfortunately). To change the protocols, you need to examine the Microsoft.SqlServer.Management.Smo.Wmi namespace (hence it comes from the "other end"):

• ServerProtocol - is a server protocol.
• ServerProtocolCollection - a collection of all protocols defined on this server.

Answer 2

# TCP/IP .

. .

:

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;
        }
    }

Answer 3

?

: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0 .

: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode

:

Answer 4

, #:

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

This does not seem like much, but it works flawlessly and instantly, without restarting the services.

Answer 5

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

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

In this you can find how to create a configuration file.