SQL Server – Protocol Used for ODBC and ADO Connection

sql server

We understand when connecting to SQL server that specifying:

< ip address>
< host or ip>,< port number>

That the ADO or ODBC client will typically use tcpip to connect.

However, we observe that when a client asks to connect to:

< server>\< instance name> that it seems the client (ODBC in this case) does NOT use tcpip.

We believe in some cases, connecting to:

< host name>

May also result in a named pipes connection.

Questions:

Since < server>\< instance name> is not connecting over tcpip, that means it is using named pipes, right?
How do I force the connection to use tcpip?

And is the behavior (of the 32 bit Windows ODBC driver for SQL Server) in this regard the same as the behavior of the old dblib client?

Best Answer

If you don't specify the protocol in the connection string, then it will use the protocol specified for the database driver. The protocol order, by default, is Shared Memory, TCP/IP and then Named Pipes. You could also set a default protocol.

To specify which protocol to use in a connection string, add tcp: before the server name. Examples: tcp:server1\instance1, tcp:server2, tcp:server3,1433

You can use SQL Server Configuration Manager to see the protocol order or regedit to see the default and protocol order.

The regkey is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0 for 64-bit applications and HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SNI11.0 for 32-bit applications.

Related Solutions

Sql-server – SQL Server ODBC Mirror/Failover Partner

I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring. I use the following connection string which allows the application to connect to the mirror after an application restart:


Dim ws As Workspace
Dim db As Database
Dim a As Long

Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseJet)
Set db = ws.OpenDatabase("C:\[pathtodatabase]", True, False, "MS Access;")
For a = 0 To db.TableDefs.Count - 1
    Me.ProgressBar1.Value = a
    DoEvents
    If db.TableDefs(a).Connect  "" Then
        db.TableDefs(a).Attributes = TableDefAttributeEnum.dbAttachSavePWD
        db.TableDefs(a).Connect = "ODBC;DRIVER=SQL Native Client;SERVER=[PrimaryServerName];Trusted_Connection=Yes;APP=[MyAppName];WSID=[MyWorkstationName];DATABASE=[SQLServerDatabase];Failover_Partner=[MirrorSQLServerName];"
        db.TableDefs(a).RefreshLink
    End If
Next a
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing

I use this code in a VB launcher app I created that the user double-clicks to launch the Access MDB/MDE.

Alternatively, this is the content of a DSN file we sometimes use to accomplish the same thing:


[ODBC]
DRIVER=SQL Native Client
APP=Microsoft Data Access Components
Trusted_Connection=Yes
SERVER=[ServerFullyQualifiedDomainName]
FAILOVER_PARTNER=[MirrorFullyQualifiedDomainName]
DATABASE=[DatabaseName]

SQLCMD in PowerShell – Connect to LocalDB

The casing of the instance name does not matter.

You need to try:

sqlcmd -S "(localdb)\MSSQLLocalDB" -d testdb01

The "localdb" part needs to be enclosed in parenthesis as that is a special syntax that points to a SQL Server Express LocalDB-specific API that allows for automatic instance start-up upon being referenced in a connection string. The whole instance name needs to be enclosed in double quotes. The command-line shown above works for me, at least outside of PowerShell.

Best Answer

Related Solutions

Sql-server – SQL Server ODBC Mirror/Failover Partner

SQLCMD in PowerShell – Connect to LocalDB

Related Question