Connecting to an Instance of SQL Server by Using SQL Server Authentication in C#

I am pretty sure that you can find many articles about how to connect to a specific database on a SQL Server instance in C#. But you won't be able to find many about how to connect to a specific SQL Server instance in C#. So thought to write a post about how to connect to a SQL Server Instance in C#.

Before starting off with the topic, I think it's better to refresh the knowledge in SQL Server instances.

Mainly there are two types of SQL Server instances. First is the Default Instance which is identified solely by the name of the computer on which the instance is running, it does not have a separate instance name. The second is the Named Instance. All instances of the database engine other than the default instance are identified by an instance name specified during installation of the instance. We can only have one default instance, but we can have many named instances and each SQL Server instance has its own copy of the server files, databases and security credentials.

Now let's move into the topic. We can connect to a SQL Server instance using SQL Server Management Objects (SMO). SMO is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

So this is how you can connect to the Default, Named and Remote Server Instances using SQL Server Authentication. First you need to add references to following dll's which can be found on "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies" folder.

1. Microsoft.SqlServer.Smo.dll
2. Microsoft.SqlServer.ConnectionInfo.dll
3. Microsoft.SqlServer.Management.Sdk.Sfc.dll

using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ConnectToSQLInstance
{
    class Program
    {
        static void Main(string[] args)
        {
            string username = "sa";
            string password = "sa";
            string instanceName = "instance_name";
            string remoteSrvName = "remote_server_name";

            /*----------------connect to default instance----------------*/
            Server oServer = new Server();
            // set to true for Windows Authentication
            oServer.ConnectionContext.LoginSecure = false;
            oServer.ConnectionContext.Login = username;
            oServer.ConnectionContext.Password = password;
            // connection is established
            Console.WriteLine(oServer.Information.Version);

            /*----------------connect to named instance----------------*/
            ServerConnection oServerConnection = new ServerConnection();
            // connects to named instance
            oServerConnection.ServerInstance = @".\" + instanceName;
            oServerConnection.LoginSecure = false;
            oServerConnection.Login = username;
            oServerConnection.Password = password;
            Server oServerNamed = new Server(oServerConnection);
            // connection is established
            Console.WriteLine(oServerNamed.Information.Version);

            /*----------------connect to default instance----------------*/
            // In here remote server name / ServerInstance needs to be specified
            ServerConnection oRemoteServerConnection = new ServerConnection(remoteSrvName);
            oRemoteServerConnection.LoginSecure = false;
            oRemoteServerConnection.Login = username;
            oRemoteServerConnection.Password = password;
            Server oServerRemote = new Server(oRemoteServerConnection);
            // connection is established
            Console.WriteLine(oServerRemote.Information.Version);
        }
    }
}

Hope this helps.

Happy Coding.

Regards,
Jaliya