I just wanted to relay some information about connectivity options for SQL Server 2008 that may be useful but are not currently documented.  While I know several of my readers already know about these, the rest of you will get a good preview of these features that will show up in the next refresh of Books Online for SQL Server 2008.

Limiting connections to a specific application

How many times have you restarted SQL Server in single user mode, only to have SQL Server Agent (or a very persistent application, or  very anxious user) steal your only connection before you have time to blink, forcing you to start the whole process over again?  This feature will help to mitigate that somewhat.  Basically you can pass a parameter to the -m switch which dictates that only a client application that represents itself using the specified name is allowed to connect.  

So if you want to allow only SQLCMD to be able to connect to SQL Server, you can use:

sqlservr.exe -m"sqlcmd"

If you are using a query window in Management Studio, you can use:

sqlservr.exe -m"Microsoft SQL Server Management Studio - Query"

Be aware that APP_NAME() and PROGRAM_NAME() are relatively easy to spoof, so in the unlikely scenario that a co-worker wants to thwart your attempt at gaining exclusive access to the server, and they know which app you are using, he/she could get in before you simply by changing their own connection string (or using the same app).  But this can work in your favor as well… for example if you want to connect via your custom application (that everyone else also uses), you can adjust the connection string for your instance of the app to use a custom name as an override.

An interesting extension to this functionality might be to dictate that only clients from a certain IP address or host name can connect.  I guess we can already accomplish this in other ways, but they require additional configuration steps (both before and after).

Named pipes when named pipes is disabled

I don't have much experience with named pipes, so I am just going to copy and paste the information I received verbatim, rather than try to sound authoritative about it.  🙂

When shared memory (a local named pipe) is enabled, local connections can connect to the instance using the named pipes protocol. To connect to the default instance, use:

osql -E -S np:.

To connect to a named instance, use:

osql -E -S \\.\pipe\MSSQL$<instance_name>\sql\query

This is how older clients such as Query Analyzer make a connection to SQL Server 2008, since they don't know how to use shared memory. If you connect locally with Query Analyzer, and run the following query, you will see that the connection is using named pipes:

    SELECT net_transport
   FROM sys.dm_exec_connections
   WHERE session_id = @@SPID;

This option will work on SQL Server 2005 as well, though there are no plans to document it in 2005 BOL.