Application Name in SQL Server when connecting from Classic ASP

Question

In my connection strings I add Application Name=XX so I can identify from the SQL server where a process is coming from (sp_who2 - ProgramName column). This works great when connecting from .NET. When I connect through Classic ASP using Server.CreateObject("ADODB.Connection"), my ProgramName is identified by SQL Server as "Internet Information Services".

Does anybody know of a way to configure something (connection string? IIS? sql?) so SQL Server sees my Application Name?

Answer 1

Just add this param:

Application Name=My app name;

Here is an example. I wouldn't suggest using a DSN or the old fashioned {SQL Server} drivers unless you're really using SQL 2000 or earlier.

conn_string = "Provider=SQLNCLI10;Data Source=x.x.x.x;Initial Catalog=dbname;" & _
              "User ID=xxx;Password=xxx;Application Name=my funky chicken;"

You may not have the most recent version of SQL Native Client, so you may need to fall back to the version-independent provider name:

conn_string = "Provider=SQLNCLI;Data Source=x.x.x.x;Initial Catalog=dbname;" & _
              "User ID=xxx;Password=xxx;Application Name=my funky chicken;"

If you don't have SQL Native Client installed, you can install it from here ( x86 | x64 ), or fall back to OLEDB:

conn_string = "Provider=SQLOLEDB.1;Data Source=x.x.x.x;Initial Catalog=dbname;" & _
              "User ID=xxx;Password=xxx;Application Name=my funky chicken;"

I tested all three connection strings above and validated that Profiler (ApplicationName), sp_who2 (ProgramName) and sys.dm_exec_sessions (program_name) showed "my funky chicken."

Answer 2

In ASP Classic parameter name should be "APP". For example:

DRIVER={SQL Server};SERVER=0.0.0.0\instancename;UID=xxx;PWD=xxx;DATABASE=xxx;App=xxx;

See details from a developer on the SQL Server Native Client team:

http://blogs.msdn.com/b/sqlnativeclient/archive/2009/05/07/sql-server-native-client-connection-strings-and-ole-db.aspx