The glue that holds the connection between an application and a SQL Server instance is just a simple string, called the “connection string”. The connection string serves a purpose much greater than simply connecting the two together. Connecting Strings have the power to influence:
- Application Security
- Application behaviour
- Help in auditing and troubleshooting
To most developers, a simple ADO connecting string would be something like:
Provider=SQLNCLI10;Data Source=WINDOWS8RPSQL2K12;Initial Catalog=AdventureWorks2012;User ID=someuser;Password=userpassword
If we try to highlight the different parts of the connection string, we see that we know the following:
- the library to use for the connection
- the data source (i.e. the instance name) to connect to
- the database to use by default once the connection has been established and,
- the credentials for a successful SQL authentication
All of the above constitute required information, and includes all the essential information about the SQL Server instance. What it does not have is the information about the calling application. To the SQL Server, the originator of the request is essentially unknown. The request could have come from almost anywhere and it would have honoured the request provided the details were correct.
Not only is this bad from a security standpoint, it is also not recommended from an auditing and troubleshooting perspective also. Let’s just see an example of this situation.
A small demo
For this demo, I launched multiple query editors within the SQL Server Management Studio, connected to the same instance of SQL Server. I then executed the same T-SQL query from these query editor windows, and attempt to identify each connection using the SQL Server Profiler (Refer tutorial here).
You can also develop a test application and run multiple instances of the application to observe a similar behaviour.
Now, we know that each query editor will open it’s own connection to the SQL Server. Therefore if the associated SPID is known, auditing is not a difficult task. In production systems, this is not the case and therefore, an alternate approach becomes necessary.
The solution – Modify your connection string!
From an auditing and troubleshooting standpoint, it is therefore, always a good practice to include the application name of the calling application and the workstation Id of the workstation. For the ADO connection string shown above, a simple modification like the following would work wonders:
Provider=SQLNCLI10;Data Source=WINDOWS8RPSQL2K12;Initial Catalog=AdventureWorks2012;User ID=someuser;Password=userpassword;Application Name=”MyTestApp”;Workstation Id=”SSMS01”
Running the same test again shows us that the Profiler can now distinguish between the calls coming in via “MyTestApp” through it’s the various sessions (represented as the HostName) v/s the calls coming in from the SQL Server Management Studio itself.
Now, the SQL Server can be programmed to only log connections not originating from certain applications, or can be programmed to respond differently when the same database/stored procedure is being executed over different connections – the possibilities are endless!
It is therefore a best practice to also set the Application Name and Workstation Id as part of the connection string of your application.
To know more about connection strings and their anatomy
A couple of years ago, I wrote a piece on SQLServerCentral.com (Connection Strings 101). That article address, at length the various components of a connection string and the areas they influence. I also provide a consolidated list of connection string components for ADO, ODBC and OLE DB libraries to download!
May the power of the connection string be with you!
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
Using HOSTNAME in this manner is likely to cause confusion. If I were to look in DNS or DHCP for ‘SSMS01’ (or just use “ping -a SSMS01”), I wouldn’t be able to find it. I might start to wonder if a rogue machine is connecting to my servers.
If the HostName is not explicitly set in the connection string, the value will default back to the client’s NETBIOS name (which can be located in DNS or DHCP). Considering how easy it is to spoof, I wouldn’t rely on it at all for auditing purposes. I certainly wouldn’t use it as the basis for branching logic that conferred or removed functionality. If you need to identify the client device, instead use the IP address (which I don’t believe can be manually set in a connection string).
A great source for connection string information is http://www.connectionstrings.com
LikeLike
Remember, different connection string starts a new connection pool. That increases number of connections on the server, and chance for connection reuse is lower. Chance of hitting instance max connection number (if it is set) and memory consumption is higher. In most situations it is better that each application has its own login. But, if you keep number of different conn strings very low, it is probably ok and is good to know that option exists.
LikeLike