Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Worst Practice - Bad Connection Strings and Bad Info in Sysprocesses

By Andy Warren,

I've discussed other worst practices (WP) in other articles here on the site - things that strike me as being so far from being a best practice it would be better to do almost anything besides what is being done! Here's a small one that can make your life a bit nicer if fixed.

Do a quick select on sysprocesses and look at the program_name column. Most of the system spids will have a null, you'll see Query Analyzer and maybe Enterprise Manager. Have any that say 'Visual Basic'? Or other generic types of names? Or if you look a little harder, any instances where you have the same hostname but a different net address? If you have some of either you're a victim of a baaaaad developer. 

How does program_name and hostname get populated? Something SQL figures out on it's own? Not at all. When you set up a connection in your application you can specify tons of different options in your connect string. Here's how you open a simple ADO connection in VB:

'set reference to Microsoft ADO

dim cn as adodb.connection

set cn= new adobb.connection

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE"

Do a select against sysprocesses now and you'll get the following values:

    Program_Name = Visual Basic
    HostName=EG (the machine I'm running this from)

Even though I didn't specify either one. If you look at the cn.connectstring property immediately after opening, you'll see it's been modified (by ADO or VB, or both), to look like this:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EG;Use Encryption for Data=False;Tag with column collation when possible=False

You can see that it's setting the workstation id to my computer name, and that the string "Visual Basic" is not included anywhere. So far we know that a generic, minimalist type connection string gets us the hostname correctly and gives us a bogus (well, less than useful) program_name.

We can fix the program_name by adding ";Application Name=OurApplicationName" to our connect string. We'll return to this in a bit.

Developers like to save time and one way they do that is by grabbing code out of other projects they have worked on. Let's say just for worst case they grab the following connect string from SomeApp and drop it into NewApp. What will you see when a 100 or some employees run it?

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EG;Use Encryption for Data=False;Tag with column collation when possible=False;Application Name=SomeApp

Answer: A hundred spids showing hostname=EG and program_name=SomeApp. We've populated the columns alright, but with the wrong values!

Here's a better way:

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=EG\ONE;Workstation ID=" & Environ$("COMPUTERNAME") & ";Application Name=" & App.Title & " v" & App.Major & "." & App.Minor & "." & App.Revision

Using the Environ$("COMPUTERNAME") function make sure we accurately report the hostname. Using App.Title plus the version information gives us not just an accurate application name, but a more informative one. You can even make it easy to add by putting this function in a code module that gets included in all your projects:

Private Function ConnectInfo() As String

ConnectInfo = ";Workstation ID=" & Environ$("COMPUTERNAME") & ";Application Name=" & App.Title & " v" & App.Major & "." & App.Minor & "." & App.Revision

End Function

Does it make a different to SQL? No. Affect performance? No. Cost a lot of time to implement? No. Make the code more complex? No. How does it make life better? Ruling out the possibility of wrong hostnames can save you a lot of energy (or headache) when you're trying to track down the user that is running the query from hell, or that you need to get disconnected so you can put the database in single user mode or whatever. Or what if you want to drop some older objects once everyone has upgraded to v2 of your app? With the version information included you can see exactly who is running what.

Really boils down to about three options. The vanilla connect string at least gets the hostname correct. A badly copied connect string can even trash that. The new and improved version can make sure you get both pieces of data correct every time.

As with all my articles..and ones about worst practices in particular...I'm looking forward to your comments!

Total article views: 15042 | Views in the last 30 days: 9
 
Related Articles
FORUM

Securing data connection information within applications

Please submit ideas for securing connection string information

FORUM

VB Connection String - ensure secure connection

How to ensure the connection string wil be made over a secure connection.

ARTICLE

Connection Strings 101

This article introduces connection strings and suggests using MDAC to easily write efficient connect...

FORUM

How to find out the source computer name if the hostname is blank

Difficulties troubleshooting issues for a particular application which does not reveal hostname from...

FORUM

Create security for an application.

How create security for an application ?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones