Skip to content

connecting-to-an-instance-of-sql-server.md

Latest commit

 

History

History
233 lines (183 loc) · 16.5 KB

connecting-to-an-instance-of-sql-server.md

File metadata and controls

233 lines (183 loc) · 16.5 KB
title Connecting to an Instance of SQL Server | Microsoft Docs
ms.custom
ms.date 08/06/2017
ms.prod sql-non-specified
ms.prod_service database-engine
ms.service
ms.component smo
ms.reviewer
ms.suite sql
ms.technology
ms.tgt_pltfrm
ms.topic reference
helpviewer_keywords
SQL Server Management Objects, connections
connections [SMO]
instances of SQL Server, connections
SMO [SQL Server], connections
ms.assetid ad3cf354-b2e3-468b-b986-1232e375fd84
caps.latest.revision 50
author stevestein
ms.author sstein
manager craigg
ms.workload On Demand

Connecting to an Instance of SQL Server

[!INCLUDEappliesto-ss-asdb-asdw-xxx-md]

The first programming step in a [!INCLUDEssNoVersion] Management Objects (SMO) application is to create an instance of the xref:Microsoft.SqlServer.Management.Smo.Server object and to establish its connection to an instance of [!INCLUDEmsCoName] [!INCLUDEssNoVersion].

You can create an instance of the xref:Microsoft.SqlServer.Management.Smo.Server object and establish a connection to the instance of [!INCLUDEssNoVersion] in three ways. The first is using a xref:Microsoft.SqlServer.Management.Common.ServerConnection object variable to provide the connection information. The second is to provide the connection information by explicitly setting the xref:Microsoft.SqlServer.Management.Smo.Server object properties. The third is to pass the name of the [!INCLUDEssNoVersion] instance in the xref:Microsoft.SqlServer.Management.Smo.Server object constructor.

Using a ServerConnection object

The advantage of using the xref:Microsoft.SqlServer.Management.Common.ServerConnection object variable is that the connection information can be reused. Declare a xref:Microsoft.SqlServer.Management.Smo.Server object variable. Then, declare a xref:Microsoft.SqlServer.Management.Common.ServerConnection object and set properties with connection information such as the name of the instance of [!INCLUDEssNoVersion], and the authentication mode. Then, pass the xref:Microsoft.SqlServer.Management.Common.ServerConnection object variable as a parameter to the xref:Microsoft.SqlServer.Management.Smo.Server object constructor. It is not recommended to share connections between different server objects at the same time. Use the xref:Microsoft.SqlServer.Management.Common.ServerConnection.Copy%2A method to get a copy of the existing connection settings.

Setting Server object properties explicitly

Alternatively, you can declare the xref:Microsoft.SqlServer.Management.Smo.Server object variable and call the default constructor. As is, the xref:Microsoft.SqlServer.Management.Smo.Server object tries to connect to the default instance of [!INCLUDEssNoVersion] with all the default connection settings.

Providing the SQL Server instance name in the Server object constructor

Declare the xref:Microsoft.SqlServer.Management.Smo.Server object variable and pass the [!INCLUDEssNoVersion] instance name as a string parameter in the constructor. The xref:Microsoft.SqlServer.Management.Smo.Server object establishes a connection with the instance of [!INCLUDEssNoVersion] with the default connection settings.

Connection Pooling

It is typically not required to call the xref:Microsoft.SqlServer.Management.Common.ConnectionManager.Connect%2A method of the xref:Microsoft.SqlServer.Management.Common.ServerConnection object. SMO will automatically establish a connection when required, and release the connection to the connection pool after it has finished performing operations. When the xref:Microsoft.SqlServer.Management.Common.ConnectionManager.Connect%2A method is called, the connection is not released to the pool. An explicit call to the xref:Microsoft.SqlServer.Management.Common.ConnectionManager.Disconnect%2A method is required to release the connection to the pool. Additionally, you can request a non-pooled connection by setting the xref:Microsoft.SqlServer.Management.Common.ConnectionSettings.NonPooledConnection%2A property of the xref:Microsoft.SqlServer.Management.Common.ServerConnection object.

Multithreaded Applications

For multithreaded applications, a separate xref:Microsoft.SqlServer.Management.Common.ServerConnection object should be used in each thread.

Connecting to an Instance of SQL Server for RMO

Replication Management Objects (RMO) uses a slightly different method from SMO to connect to a replication server.

RMO programming objects require that a connection to an instance of [!INCLUDEssNoVersion] is made by using the xref:Microsoft.SqlServer.Management.Common.ServerConnection object implemented by the Microsoft.SqlServer.Management.Common namespace. This connection to the server is made independently of an RMO programming object. It is then it is passed to the RMO object either during instance creation or by assignment to the xref:Microsoft.SqlServer.Replication.ReplicationObject.ConnectionContext%2A property of the object. In this manner, an RMO programming object and the connection object instances can be created and managed separately, and a single connection object can be reused with multiple RMO programming objects. The following rules apply for connections to a replication server:

  • All properties for the connection are defined for a specified xref:Microsoft.SqlServer.Management.Common.ServerConnection object.

  • Each connection to an instance of [!INCLUDEssNoVersion] must have its own xref:Microsoft.SqlServer.Management.Common.ServerConnection object.

  • All authentication information to make the connection and successfully log on to the server is supplied in the xref:Microsoft.SqlServer.Management.Common.ServerConnection object.

  • By default, connections are made by using Microsoft Windows Authentication. To use [!INCLUDEssNoVersion] Authentication, xref:Microsoft.SqlServer.Management.Common.ConnectionSettings.LoginSecure%2A must be set to False and xref:Microsoft.SqlServer.Management.Common.ConnectionSettings.Login%2A and xref:Microsoft.SqlServer.Management.Common.ConnectionSettings.Password%2A must be set to a valid [!INCLUDEssNoVersion] logon and password. Security credentials must always be stored and handled securely, and supplied at run time whenever possible.

  • The xref:Microsoft.SqlServer.Management.Common.ConnectionManager.Connect%2A method must be called before passing the connection to any RMO programming object.

Examples

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

Connecting to the Local Instance of SQL Server by Using Windows Authentication in Visual Basic

Connecting to the local instance of [!INCLUDEssNoVersion] does not require much code. Instead, it relies on default settings for authentication method and server. The first operation that requires data to be retrieved will cause a connection to be created.

This example is Visual Basic .NET code that connects to the local instance of SQL Server by using Windows Authentication.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'The connection is established when a property is requested.
Console.WriteLine(srv.Information.Version)
'The connection is automatically disconnected when the Server variable goes out of scope.

Connecting to the Local Instance of SQL Server by Using Windows Authentication in Visual C#

Connecting to the local instance of [!INCLUDEssNoVersion] does not require much code. Instead, it relies on default settings for authentication method and server. The first operation that requires data to be retrieved will cause a connection to be created.

This example is Visual C# .NET code that connects to the local instance of [!INCLUDEssNoVersion] by using Windows Authentication.

{   
//Connect to the local, default instance of SQL Server.   
Server srv;   
srv = new Server();   
//The connection is established when a property is requested.   
Console.WriteLine(srv.Information.Version);   
}   
//The connection is automatically disconnected when the Server variable goes out of scope.

Connecting to a Remote Instance of SQL Server by Using Windows Authentication in Visual Basic

When you connect to an instance of [!INCLUDEssNoVersion] by using Windows Authentication, you do not have to specify the authentication type. Windows Authentication is the default.

This example is [!INCLUDEvbprvb] .NET code that connects to the remote instance of [!INCLUDEssNoVersion] by using Windows Authentication. The string variable strServer contains the name of the remote instance.

'Connect to a remote instance of SQL Server.
Dim srv As Server
'The strServer string variable contains the name of a remote instance of SQL Server.
srv = New Server(strServer)
'The actual connection is made when a property is retrieved. 
Console.WriteLine(srv.Information.Version)
'The connection is automatically disconnected when the Server variable goes out of scope.

Connecting to a Remote Instance of SQL Server by Using Windows Authentication in Visual C#

When you connect to an instance of [!INCLUDEssNoVersion] by using Windows Authentication, you do not have to specify the authentication type. Windows Authentication is the default.

This example is Visual C# .NET code that connects to the remote instance of [!INCLUDEssNoVersion] by using Windows Authentication. The string variable strServer contains the name of the remote instance.

{   
//Connect to a remote instance of SQL Server.   
Server srv;   
//The strServer string variable contains the name of a remote instance of SQL Server.   
srv = new Server(strServer);   
//The actual connection is made when a property is retrieved.   
Console.WriteLine(srv.Information.Version);   
}   
//The connection is automatically disconnected when the Server variable goes out of scope.

Connecting to an Instance of SQL Server by Using SQL Server Authentication in Visual Basic

When you connect to an instance of [!INCLUDEssNoVersion] by using [!INCLUDEssNoVersion] Authentication, you must specify the authentication type. This example demonstrates the alternative method of declaring a xref:Microsoft.SqlServer.Management.Common.ServerConnection object variable, which enables the connection information to be reused.

The example is [!INCLUDEvbprvb] .NET code that demonstrates how to connect to the remote and vPassword contain the logon and password.

' compile with:   
' /r:Microsoft.SqlServer.Smo.dll  
' /r:Microsoft.SqlServer.ConnectionInfo.dll  
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll   
  
Imports Microsoft.SqlServer.Management.Smo  
Imports Microsoft.SqlServer.Management.Common  
  
Public Class A  
   Public Shared Sub Main()  
      Dim sqlServerLogin As [String] = "user_id"  
      Dim password As [String] = "pwd"  
      Dim instanceName As [String] = "instance_name"  
      Dim remoteSvrName As [String] = "remote_server_name"  
  
      ' Connecting to an instance of SQL Server using SQL Server Authentication  
      Dim srv1 As New Server()   ' connects to default instance  
      srv1.ConnectionContext.LoginSecure = False   ' set to true for Windows Authentication  
      srv1.ConnectionContext.Login = sqlServerLogin  
      srv1.ConnectionContext.Password = password  
      Console.WriteLine(srv1.Information.Version)   ' connection is established  
  
      ' Connecting to a named instance of SQL Server with SQL Server Authentication using ServerConnection  
      Dim srvConn As New ServerConnection()  
      srvConn.ServerInstance = ".\" & instanceName   ' connects to named instance  
      srvConn.LoginSecure = False   ' set to true for Windows Authentication  
      srvConn.Login = sqlServerLogin  
      srvConn.Password = password  
      Dim srv2 As New Server(srvConn)  
      Console.WriteLine(srv2.Information.Version)   ' connection is established  
  
      ' For remote connection, remote server name / ServerInstance needs to be specified  
      Dim srvConn2 As New ServerConnection(remoteSvrName)  
      srvConn2.LoginSecure = False  
      srvConn2.Login = sqlServerLogin  
      srvConn2.Password = password  
      Dim srv3 As New Server(srvConn2)  
      Console.WriteLine(srv3.Information.Version)   ' connection is established  
   End Sub  
End Class

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

When you connect to an instance of [!INCLUDEssNoVersion] by using [!INCLUDEssNoVersion] Authentication, you must specify the authentication type. This example demonstrates the alternative method of declaring a xref:Microsoft.SqlServer.Management.Common.ServerConnection object variable, which enables the connection information to be reused.

The example is Visual C# .NET code that demonstrates how to connect to the remote and vPassword contain the logon and password.

// compile with:   
// /r:Microsoft.SqlServer.Smo.dll  
// /r:Microsoft.SqlServer.ConnectionInfo.dll  
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll   
  
using System;  
using Microsoft.SqlServer.Management.Smo;  
using Microsoft.SqlServer.Management.Common;  
  
public class A {  
   public static void Main() {   
      String sqlServerLogin = "user_id";  
      String password = "pwd";  
      String instanceName = "instance_name";  
      String remoteSvrName = "remote_server_name";  
  
      // Connecting to an instance of SQL Server using SQL Server Authentication  
      Server srv1 = new Server();   // connects to default instance  
      srv1.ConnectionContext.LoginSecure = false;   // set to true for Windows Authentication  
      srv1.ConnectionContext.Login = sqlServerLogin;  
      srv1.ConnectionContext.Password = password;  
      Console.WriteLine(srv1.Information.Version);   // connection is established  
  
      // Connecting to a named instance of SQL Server with SQL Server Authentication using ServerConnection  
      ServerConnection srvConn = new ServerConnection();  
      srvConn.ServerInstance = @".\" + instanceName;   // connects to named instance  
      srvConn.LoginSecure = false;   // set to true for Windows Authentication  
      srvConn.Login = sqlServerLogin;  
      srvConn.Password = password;  
      Server srv2 = new Server(srvConn);  
      Console.WriteLine(srv2.Information.Version);   // connection is established  
  
      // For remote connection, remote server name / ServerInstance needs to be specified  
      ServerConnection srvConn2 = new ServerConnection(remoteSvrName);  
      srvConn2.LoginSecure = false;  
      srvConn2.Login = sqlServerLogin;  
      srvConn2.Password = password;  
      Server srv3 = new Server(srvConn2);  
      Console.WriteLine(srv3.Information.Version);   // connection is established  
   }  
}

See Also

xref:Microsoft.SqlServer.Management.Smo.Server
xref:Microsoft.SqlServer.Management.Common.ServerConnection