Report Data Source Refuses to point to correct database

• Edward Mlynar

  SSCommitted

  Points: 1723

  More actions

  March 19, 2013 at 6:40 am

  #291247

  I have a bunch of reports that point to a production database using a shared data source. For this example, we will say DataSource1 points to ProdDB1 with user ProdDBUser.

  I moved ProdDB1 to new disks by using the following method:

  1 - Backup database.

  2 - Restore database into ProdDB1_Restore.

  3 - Stop all applications from accessing ProdDB1.

  4 - Backup ProdDB1 tlog.

  5 - RENAME ProdDB1 TO ProdDB1_DONOTUSE .

  6 - Restore tlog backup into ProdDB1_Restore.

  7 - RENAME ProdDB1_Restore to ProdDB1

  8 - Disable login access to ProdDB1_DONOTUSE

  9 - Bring apps back online.

  Everything works great with every other application and login. Go to reporting services, and it is trying to connect to ProdDB1_DONOTUSE. It is the strangest thing I've ever seen. It's happening from the reporting server and from BIDS on my desktop.

  I have deleted and recreated the ProdDBUser. I even deleted and recreated the datasource. I can log into the database as the ProdDBUser and it execute the same query in the report directly into the SSMS and it hits the correct database. This is only occurring in SSRS.

  Here is what is even more bizarre... the ProdDBUser doesn't even hit the ProdDB1 database directly. It connects to a ProdReporting database that has a passthrough query to ProdDB1.

  How did SSRS get the database name ProdDB1_DONOTUSE?

  ...