• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1502
  • Last Modified:

Can only see SQL Server System DBs when creating an ODBC DSN Connection

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.

Thanks!
0
bosshognz
Asked:
bosshognz
  • 4
  • 3
3 Solutions
 
JaseemKCommented:
You only see system databases because your SQL account hasn't been granted access to the required user databases.
0
 
bosshognzAuthor Commented:
I think I see what you mean.  The user has permissions within the database, but at the SQL Server>Security>Login level they only have public access.
I've looked at the live DB and they have db_owner access there, but when I try to assign them that role, I get 'Create failed for User 'admin''  .... 'already exists in the current database'.

As I said earlier, this is my first time installing SQL Server - albeit in a test enviro, do I need to assign security prior to restoring the database?  Can you actually do that?  What step am I missing?
0
 
tigin44Commented:
1. by backing up and restoring you transfer the database user accounts but the server logins do not transfered. you should transfer the logins manually. check this link

http://support.microsoft.com/kb/246133

2. did you check the user database mapping? if the current user is not mapped to the databses you want to reach that will not be lssted sn the dns defition. You should do that too.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
bosshognzAuthor Commented:
That applied to very old versions of SQL - I found the one related to 2005/2008:
http://support.microsoft.com/kb/918992 - very similar if not the same.  My question is now - the live database (A) will still be live afterwards.  Will running these scripts have any effect on the live database?

The User Mapping was what I was looking at - and I'm getting the  'Create failed for User 'admin''  .... 'already exists in the current database' error there.
0
 
tigin44Commented:
running this code will make the users of system to reach the databases..
If any of the users are created you may get a message like the one you get... just skip those scripts and run the rest...
0
 
bosshognzAuthor Commented:
I don't understand you sorry.  My question is simple.  Will running that script have an impact on my source SQL Server, which is still running and will continue to run.
I don't want ANY impact at all.  I'm merely setting up a test environment, not doing a full transfer from one server to another.
0
 
tigin44Commented:
simply will not have any impact on the running sytem... It just scans the system tables and generates the create scripts of the logins...
0
 
bosshognzAuthor Commented:
Used the script to get the users out of the source SQL server.  Had to delete the DB on the destination server and then remove ODBC connections and delete the user on the destination server before it would work.  DB now shows up in ODBC connections.  

Thanks.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now