Note in Font

Creator Code 39 Extended in Font Note

The Dedicated Administrator Connection (DAC) is especially designed for use when a server instance is unresponsive to other connection types, such as Windows or SQL Server authentication. Although the DAC integrates tightly with the sqlcmd utility, which is available through SQL Server Express, the DAC feature does not operate with SQL Server Express instances.

Figure 2-24 shows a command prompt window with a sqlcmd session. The session starts by invoking the sqlcmd.exe with the S switch. The instance name references the SQLEXPRESS instance on the local server. This statement attempts to connect via Windows authentication, which is the default authentication route for sqlcmd. After the login completes, the session continues by running a simple query. This task confirms that the connection is operational. Three additional sqlcmd statements on lines 1>, 2>, and 3> return a result set with the value of the number of salespersons in the SalesPerson table within the Sales schema of the AdventureWorks database. The result set appears after the GO keyword, which launches the current batch of statements and readies sqlcmd for a new batch. The 1> prompt that appears after the result set shows sqlcmd is ready to start a new batch of statements. The EXIT keyword closes the sqlcmd session and returns control to the operating system command prompt window.

Figure 2-24. You only need to specify the server instance name to connect from sqlcmd with Windows authentication.

When using SQL Server authentication, you need to specify a login name and its password. Most database applications refer to a login name with userID or userid. Therefore, sqlcmd names its switch for the login name -U. There are several ways to specify the password for a login. For example, there is a -P switch for designating the password value when you invoke sqlcmd. However, sqlcmd shows your password in clear text so that anyone who can view your screen can learn your password. Another approach is to omit the -P switch when connecting with SQL Server authentication. In this scenario, sqlcmd prompts for the password after processing the sqlcmd statement but before granting access to a server instance. When you type your password into the prompt, no onscreen feedback appears. Thus, your password remains secure from those who can view your screen. Figure 2-25 shows the syntax for a sqlcmd session using SQL Server authentication. The session uses the sa login. Notice that you still need to specify a server instance name with the -S switch. In addition, you need to designate a login name, which is sa, in the figure. The sqlcmd statement syntax without a P switch causes the Password: prompt. A user must enter a valid password for the login specified with the -U switch before the sqlcmd utility permits access to the server. The display of the 1> prompt after the Password: prompt confirms the granting of server access. Although you must enter a valid password to get to the 1> prompt, no password shows on the screen. Figure 2-25 demonstrates the use of the :serverlist keyword. Recall that this statement returns a list of visible SQL Server instances. If you are not sure of an instance name or you want to confirm that an instance is broadcasting its availability, this command is very convenient. The command executes automatically so there is no need for the GO keyword. The figure concludes with the EXIT keyword, which closes the sqlcmd session.

Figure 2-25. By not using the -P switch with SQL Server authentication, you can secure your password from those who can view your screen.

The whole purpose for connecting to a SQL Server instance with sqlcmd is to perform database chores namely, those that you can perform with T-SQL and sqlcmd statements. The sqlcmd utility is natively best for those chores that have short command statements and generate little or no output to the screen. An administrative statement, such as CREATE LOGIN, is ideal for the sqlcmd utility. Another example is any SELECT statement that generates an aggregate. The T-SQL statements in Figure 2-6 show how convenient it is to count the unique values in a column with the sqlcmd utility.