当前位置:主页 > 资料 >

Different Ways to Connect to SQL Server Using sqlcmd
栏目分类:资料   发布日期:2017-10-31   浏览次数:

导读:本文为去找网小编(www.7zhao.net)为您推荐的Different Ways to Connect to SQL Server Using sqlcmd,希望对您有所帮助,谢谢! Problem You need to connect to a SQL Server instance using sqlcmd. At first it seems to be an

本文为去找网小编(www.7zhao.net)为您推荐的Different Ways to Connect to SQL Server Using sqlcmd,希望对您有所帮助,谢谢!

copyright www.7zhao.net



www.7zhao.net

Problem

You need to connect to a SQL Server instance using sqlcmd. At first it seems to be an easy task, but sometimes things can get complicated. To mention a few of these things: How do you connect to a SQL Server instance that does not listen on a TCP port? How do you connect to a SQL Server instance if your host machine cannot resolve the server DNS? What about named instances? Keep reading this tip, so you can answer these questions. 欢迎访问www.7zhao.net

Solution

Those of us that have been in the SQL Server world for many years often disregarded sqlcmd. Let’s face it; we are used to graphic user interfaces, like (SSMS). But now that SQL Server has entered the Linux world we must adapt to use the command line, usually referred to as a shell amongst the Linux community. 去找(www.7zhao.net欢迎您

We need to get used to seeing more SQL Server instances on Linux in the coming years. Usually production Linux servers do not have a graphical interface and there is no Remote Desktop Connection, so everything is done within a SSH connection. 本文来自去找www.7zhao.net

SQL Server Connection Protocols

SQL Server allows three different connection protocols that I will describe later in this tip: 本文来自去找www.7zhao.net

  • TCP/IP
  • Named Pipes
  • Shared Memory

When we connect to a SQL Server instance using SSMS, we just enter the server name in the log in screen, select the authentication method and then connect and then we can start using SSMS.

欢迎访问www.7zhao.net

If we want to use a specific protocol when connecting with SSMS we just need to select the appropriate protocol from a combo box like on the image below. 欢迎访问www.7zhao.net

www.7zhao.net

But when we use the console to connect and need to use a specific protocol, we must do this without a combo box.

去找(www.7zhao.net欢迎您

Connecting with sqlcmd using a Specific Protocol

Most of the time when we connect to a SQL Server instance we don’t specify a protocol and you may think that you are using the TCP/IP protocol. Well, let me tell you that if you don’t specify the connection protocol then you don’t have a guarantee that you are using TCP/IP. There is a protocol order that SSMS and sqlcmd have which is determined by the protocol order in SQL Server Configuration Manager.

www.7zhao.net

On the following image I show you my protocol order. But there is something really important! Please realize that there are two Client Configurations. One is for 32 bit applications and the other is for 64 bit applications. This is especially important if you are using , because you may be using a 32 bit connector, but if the package runs on 64 bit version and you haven’t configured the 64 bit settings, the package will fail.

内容来自www.7zhao.net

本文来自去找www.7zhao.net

SQL Server TCP/IP Connection with sqlcmd

TCP/IP is well known to us. In order to connect to a SQL Server instance using the TCP/IP protocol you need the server’s IP address or the host name and the listening port if your instance doesn’t run on the default port. 内容来自www.7zhao.net

We just need to specify the server name or its IP address and in some cases the port number. The following is the general syntax to connect using TCP/IP: 去找(www.7zhao.net欢迎您

sqlcmd -S tcp:<computer name>,<port number> 本文来自去找www.7zhao.net 

Notice that we add tcp: prior to the server address. I mean, if you just enter an IP address you don’t have a guarantee that you will use the TCP/IP protocol. For example if you want to connect using the TCP/IP protocol to a server named SQL-A using SQL Server’s default instance with Windows Authentication, the syntax will be as follows: 本文来自去找www.7zhao.net

sqlcmd –S tcp:SQL-A 去找(www.7zhao.net欢迎您 

If you want to use SQL Server authentication then you need to specify the user name and password as follows:

www.7zhao.net

sqlcmd –S tcp:SQL-A –U sa –P Pa$$w0rd 
copyright www.7zhao.net

When you cannot resolve the host name of the server you should use the IP address instead of the host name. In such case, all the previous commands will look like this assuming 10.10.10.10 is the server’s IP address: 本文来自去找www.7zhao.net

sqlcmd –S tcp:10.10.10.10
sqlcmd –S tcp:10.10.10.10 –U sa –P Pa$$w0rd 

本文来自去找www.7zhao.net

For a named instance (in this case an instance named TEST) the previous commands are as follows: www.7zhao.net

sqlcmd –S tcp:SQL-A\TEST
sqlcmd –S tcp:SQL-A\TEST –U sa –P Pa$$w0rd 内容来自www.7zhao.net 

Also, you may need to specify the listening port. In such case you must specify the port after a comma. For example, suppose that my default instance uses port 1433 and the TEST instance runs on port 51613, then your connection string will be as follows:

去找(www.7zhao.net欢迎您

sqlcmd –S tcp:SQL-A,1433
sqlcmd –S tcp:SQL-A,1433 –U sa –P Pa$$w0rd
 
sqlcmd –S tcp:10.10.10.10,1433
sqlcmd –S tcp:10.10.10.10,1433 –U sa –P Pa$$w0rd
 
sqlcmd –S tcp:SQL-A,51613
sqlcmd –S tcp:SQL-A,51613 –U sa –P Pa$$w0rd
 
sqlcmd –S tcp:10.10.10.10,51613
sqlcmd –S tcp:10.10.10.10,51613 –U sa –P Pa$$w0rd 

www.7zhao.net

copyright www.7zhao.net

SQL Server Named Pipes Connection with sqlcmd

A named pipe is a named, one-way or duplex pipe for communication amongst a server and a client. Internally, all instances of a named pipe have the same pipe name, but they keep their own buffers that allow message-based communication and client impersonation. Named Pipes relies on Inter Process Communication (IPC). There is something to note which is the fact that if you connect to a local instance using Named Pipes, that pipe will run in Kernel mode as a Local Procedure Call (LPC).

欢迎访问www.7zhao.net

Usually this protocol is not used because when connecting to a remote SQL Server instance it is preferred to use the TCP/IP protocol. On the other hand, when connecting to a local instance Shared Memory is often the protocol of choice.

www.7zhao.net

The syntax of a named pipe connection for the default instance is as follows:

本文来自去找www.7zhao.net

\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query www.7zhao.net 

For example, a named pipe to the default instance of server MYSERVER will be like this:

欢迎访问www.7zhao.net

\\MYSERVER\pipe\sql\query 
欢迎访问www.7zhao.net

On the other hand, the syntax for a named instance looks as follows:

欢迎访问www.7zhao.net

\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query 

www.7zhao.net

As an example, if we want to use a named pipe to the SQL Server instance TEST on server SQL-A, the syntax will be like this:

内容来自www.7zhao.net

\\SQL-A\pipe\MSSQL$TEST\sql\query 

copyright www.7zhao.net

Connecting to a SQL Server instance by using the named pipes protocol is not too different than using TCP/IP. I can say that we must add np: prior to the server address, but it is incorrect or at least incomplete. When you connect using named pipes you don’t connect to a server. Instead you connect to a pipe, that’s why this protocol is called named pipes. 欢迎访问www.7zhao.net

In the previous section I explained how to build a pipe, now in the following piece of code I will show you the way to connect to a SQL Server default instance and to a named instance using both Windows and SQL Server authentication.

内容来自www.7zhao.net

sqlcmd –S np:\\SQL-A\pipe\sql\query
sqlcmd –S np:\\SQL-A\pipe\sql\query –U sa –P Pa$$w0rd
sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query
sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query –U sa –P Pa$$w0rd 内容来自www.7zhao.net 

去找(www.7zhao.net欢迎您

SQL Server Shared Memory Connection with sqlcmd

This protocol only can be used when the client connecting runs on the local server. Basically it is a Local Procedure Call (LPC) that runs in Kernel Mode. Also, if we think about the previous explanation of named pipes, we can easily realize that shared memory is a special case of named pipes.

去找(www.7zhao.net欢迎您

Something to note is that if you are using MDAC 2.8 or earlier, you cannot use the shared memory protocol. If you try to use this protocol, sqlcmd will automatically switch to the named pipes protocol.

www.7zhao.net

If we think about it, the fact that MDAC 2.8 automatically translates shared memory to named pipes makes sense with the idea that shared memory is a special case of a named pipe.

去找(www.7zhao.net欢迎您

The prefix to use Shared Memory is lpc:. Remember I told you that Shared Memory is a Local Procedure Call. In this case we don’t need to worry about if we are able to resolve the host name, shared memory only works locally and you will always resolve to the local server name. www.7zhao.net

sqlcmd –S lpc:SQL-A
sqlcmd –S lpc:SQL-A –U sa –P Pa$$w0rd
sqlcmd –S lpc:SQL-A\TEST
sqlcmd –S lpc:SQL-A\TEST –U sa –P Pa$$w0rd 

copyright www.7zhao.net

本文来自去找www.7zhao.net

Next Steps

  • If this was the first time you heard of sqlcmd, my previous tip will serve as an introduction: .
  • Check out this tip where we covered the basics on how to connect to SQL Server with sqlcmd: .
  • You can get more information about the connection protocols of SQL Server in this tip:
  • If you are running Linux then this tip will guide you to install sqlcmd: .
  • You can also use sqlcmd in SQL Server Management Studio by using the sqlcmd mode: .
  • In case you need to audit the connection protocols allowed by each of your SQL Server instances, the following tip will serve as a starting point: .

Last Update:

内容来自www.7zhao.net

本文来自去找www.7zhao.net

www.7zhao.net

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

内容来自www.7zhao.net

Related Resources

内容来自www.7zhao.net

欢迎访问www.7zhao.net


本文原文地址:https://www.mssqltips.com/sqlservertip/5133/different-ways-to-connect-to-sql-server-using-sqlcmd/

以上为Different Ways to Connect to SQL Server Using sqlcmd文章的全部内容,若您也有好的文章,欢迎与我们分享! 本文来自去找www.7zhao.net

下一篇:没有了
Copyright ©2008-2017去找网版权所有   皖ICP备12002049号-2 皖公网安备 34088102000435号   关于我们|联系我们| 免责声明|友情链接|网站地图|手机版