1

Cannot find old connection string connecting to old database and not new one that has been defined. We have changed web servers on Azure and cannot find the reference to the old database because our IIS sites are still hitting the old database as well as the newly defined one.

I am hoping this is not edge case and someone has had this problem. There are a lot of variables to this problem and know anyone reading this will have questions. Our new sites are working on new SQL but when we shut down the old one our sites break and error out. I am thinking something to do with session state and checked all the conn strings in IIS and found no reference whatsoever to the old SQL Server.

2

To add on to what Max is saying, you could use the following to get you the sql info and use the port information and sql text to figure out which service is doing what and then use those additional clues to help you isolate where it's coming from.

SELECT 
   des.host_name
    , des.program_name
    , des.login_name
    , dec.client_net_address
    , dec.client_tcp_port
    , db_name(a.dbid) as db_name
    , object_name(a.objectid) as object_name
    , a.text as sql_text
FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) a
WHERE des.is_user_process = 1;
| improve this answer | |
  • I think I have it drilled down to being an ASP State issue. Where might a developer hide said conn strings outside of the normal places? – JaxCoder Apr 15 '16 at 18:07
  • I've seen people use environment variables, sometimes for direct and/or indirect configurations. – codedawg82 Apr 15 '16 at 18:18
  • good call, I will look into the environmental variables. What if the same thing is happening on both web servers and the new one is doing the same thing. I am also thinking conn strings are hardcoded in the application somewhere. – JaxCoder Apr 15 '16 at 18:30
  • "conn strings are hardcoded in the application somewhere" -- ¯_(ツ)_/¯ if you can't re-deploy the application without adding a logging utility of some sort, you're going to be spending quite some time debugging this. – codedawg82 Apr 15 '16 at 19:00
  • I found a couple of exe files the registered and de-registered asp state for iis and sql server. I ran those on each server to remove the old and add the new sql server to asp state. I am going to continue to look further into this by heading to any environmental variables that may be implemented. – JaxCoder Apr 18 '16 at 15:34
1

This doesn't directly answer your question, however it may help you identify the specific parts that are still accessing the old database server.

Run the following query on the old server:

SELECT des.host_name
    , des.program_name
    , des.login_name
    , dec.client_net_address
FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
WHERE des.is_user_process = 1;

The results will show you the currently connected sessions, with some pertinent info.

| improve this answer | |
  • I need a way to determine what the object ID references exactly from the program/application. The program name it gives is .Net SQLClient Data Provider and I have four applications using the same username. Not my setup, just what I have to work with... – JaxCoder Apr 15 '16 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.