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?

Solution 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."

Solution 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

Comments

[Remus Rusanu]

+1 Here is the relevant link: msdn.microsoft.com/en-us/library/ms130822.aspx

[Dingels35]

This works in .NET, but not with Server.CreateObject("ADODB.Connection") in Classic ASP

[Adir D]

What version of SQL Server? What provider are you using? Can you show your actual connection string? If you're using a DSN, stop doing that.

[Dingels35]

@Aaron Bertand - we are on 2008R2. Here's the string - DRIVER={SQL Server};SERVER=0.0.0.0\instancename;UID=xxx;PWD=xxx;DATABASE‌​=xxx;Application Name=xxx;

[Adir D]

@Dingels35 see my update, please use more modern connection strings and appropriate providers. Not only do they support all of the attributes that have been added since ODBC was the rage, they fix a lot of issues and probably perform a bit better, too.

[Dingels35]

@Aaron Bertand - THANK YOU - we've obviously been using the same connection string for almost 10 years. It worked well through our SQL Server migrations but obviously it is outdated. I'm seeing the info I was hoping for now.

[Dingels35]

Quick update - ODBC connections will not pass the application name, but ADO connections will. But when calling a stored procedure from ASP that has multiple statements and does not have "SET NOCOUNT ON", there are issues returning a recordset through ADO. In order to use the ADO connection string so we get the application name to our database, we needed to update all of our sp's to include SET NOCOUNT ON. (I know - this is a best practice anyway.)

[Jhonatan Muller]

Work for PHP + ODBC too