Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ODBC connection is invalid after restoring DB to a new SQL Server Express machine

Posted on 2012-03-13
7
Medium Priority
?
493 Views
Last Modified: 2012-03-14
Short story: after restoring a SqlExpress 2005 LOB db to a new server, the client application cannot connect anymore to the instance (invalid connection).

So I have a LOB db hosted on a sqlexpress 2005 server (to be retired) that has to be moved to a new server. I installed the same version LOB db on the new server: it installed the same sqlexpress 2005 engine (9.0.3042) and created a new empty db. I install the application on a workstation and establish a ODBC connection with the new server. So I have access to the new empty db, where I can start entering data. I then made a backup of the db on the old server using Management Studio / right-clic on db / backup / create new file / and save to a .bak file. In management studio on the new server, I restore the bakcup file to the new empty db using the Tasks / Restore / select .bak file. Restore runs without error.
But then the client workstation cannot connect anymore to the Instance hosting the restored file: ODBC connection is invalid.

I tried to detach the DB from the old server and then attach it to the new without success. Tried with different versions of SQL Express 2005 also without success. Do I need to restore also the system databases (master, model, msdb, tempdb), or proceed differently ?

Environment info: Old server = sbs2003 running Sqlexpress 2005 v.9.0.3042. New server = sbs2011 running Sqlexpress 2005 v9.0.3042 (has also Sql express 2008 R2 running for other instances (sharepoint, wsus).

Thanks.
0
Comment
Question by:ndidomenico
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 37717297
What is exact error message? Assuming that your ODBC connection is valid, there could be quite a few different reasons. Most common ones are:
1. Firewall
2. SQL Express has network protocols disabled by default. You need to go to server configuration utility, enable them there and restart the service

Also I'd suggest you to try to connect to the instance via Management Studio rather than use ODBC to make sure that there are no problems in ODBC configuration
0
 

Author Comment

by:ndidomenico
ID: 37717328
The error is "ODBC connection invalid". The connection to that database and instance works fine until I do a restore of the database from the backup file. The backup file is restored under the same instance where the new empty database was working just minutes before the restore.
0
 
LVL 13

Accepted Solution

by:
dwkor earned 2000 total points
ID: 37717348
Are you using SQL Authentication? In such case make sure that login is mapped to the database user because restore would invalidate that. You can "fix" the problem with sp_change_user_login stored procedure http://msdn.microsoft.com/en-us/library/ms174378.aspx

Error message is way too generic. This is a reason why I suggested to use management studio to test connection first.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:ndidomenico
ID: 37717351
I've allowed the sqlserv.exe of that instance in the Windows firewall, enabled local AND remote in the SQL Surface Area Configuration, started the SQL browser service and allowed sqlbrowser.exe in the firewall.
0
 

Author Comment

by:ndidomenico
ID: 37717357
You mean to use Management Studio from the workstation and try to connect to the sql server ? Will check your last 2 recommendations and get back in a few minutes
0
 
LVL 13

Expert Comment

by:dwkor
ID: 37717683
You can use Management studio on the same box - it will show you if there is obvious problem (for example - login does not have access to the database) but would not help to troubleshoot security problem.

So the better way to use Management Studio (you can install it without SQL Express itself) on the box from which you're trying to connect via odbc
0
 

Author Closing Comment

by:ndidomenico
ID: 37720560
The problem was SQL Authentication. I finally found the proper login name (sa) and password to use when linking to the DB. Your suggestion on using Management Studio to try to login to the Instance got me in the right direction, along with your asking if I was using SQL authentication.

Thanks a lot !
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are a user of the discontinued Microsoft Office Accounting 2008 (MSOA) and have to move to a new computer running Windows 8, you will be unhappy to discover that it won't install.  In particular, Microsoft SQL Server 2005 Express Edition (SSE…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Loops Section Overview

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question