8

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.

IrfanRaza
  • 3,030
  • 17
  • 64
  • 89

5 Answers5

9

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
AlexS
  • 2,388
  • 15
  • 15
  • Thanks Alex for your quick reply. – IrfanRaza Feb 15 '10 at 15:00
  • Was going to suggest this but I haven't found where in SMO you can change connection protocols. But, its the most likely place to find them. –  Feb 15 '10 at 15:01
  • Thanks Will, I will try to dig more into SMO. – IrfanRaza Feb 15 '10 at 15:03
  • Alex where should i get this SMO or rather which assembly should i use to get programming support in c#? – IrfanRaza Feb 15 '10 at 15:10
  • You will actually need a bunch of 'em (assemblies). They come with SQL Server (naturally) but can be installed separately. See this article: http://code.google.com/p/dbbuilder/wiki/InstallingPrerequisites – AlexS Feb 15 '10 at 15:19
  • It would be a lot easier if Microsoft had proper documentation for this :( – Imran Faruqi May 22 '21 at 08:09
5

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;
        }
    }
Community
  • 1
  • 1
Reza Ameri
  • 1,803
  • 3
  • 24
  • 32
  • 3
    This was a great solution and required a couple of things: 1) Add references to Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SqlEnum, Microsoft.SqlServer.SqlWmiManagement, and Microsoft.SqlServer.WmiEnum found in C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies 2) Add using Microsoft.SqlServer.Management.Smo and using Microsoft.SqlServer.Management.Smo.Wmi. – Chris Schiffhauer Jan 29 '13 at 20:34
  • Great solution. In addition to @PaulyGlott comment, I also needed to add a line: `srv.Settings.Alter();` after `srv.Settings.LoginMode = ServerLoginMode.Mixed;`, otherwise, it wouldn't save the changed setting. – André Santaló Nov 28 '13 at 16:39
4

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.

Chris Schiffhauer
  • 17,102
  • 15
  • 79
  • 88
4

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

dugas
  • 12,025
  • 3
  • 45
  • 51
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.

Jonathan
  • 11,809
  • 5
  • 57
  • 91