Can only see SQL Server System DBs when creating an ODBC DSN Connection
Posted on 2011-05-05
I am currently testing our LOB applications in Server 2008 R2 with the intention of upgrading from Server 2003 environment to Server 2008 R2. This also includes moving from SQL Server 2005 to SQL Server 2008 R2.
I have set up the new system myself. This was my first attempt at installing SQL Server, so it may well be that I've got some of the initial config wrong, I really don't know.
I made a backup of the live DB and restored it onto the new SQL Server - that went without a hitch. All the permissions/users etc transferred across.
The next step was to create the DSN connection through the ODBC Data Source Administrator. I add a Systen DSN, select 'SQL Server' for the driver, give it the appropraite name and description, and select the correct SQL Server to connect to, I use SQL Authentication and use the correct user for this database (I have followed the application's instructions to the letter about 5 times over!).
This next step I think is a symptom of my problem. When I go to select the default database (this is an optional step in the instructions), I only see the System Databases (master, msdb tempdb). None of the 'normal' databases are in the list, including the one I want.
If I untick the 'Change the default database to', and I try to run the program, it tells me that my database is offline, and doesn't work.