Need help connecting to a restored SQL database

Posted on 2011-03-07
Last Modified: 2012-05-11
I have a production environment which consists of an application/admin server and a SQL 2005 hosting the database on the backend.

I also have a test environment with the same setup.
4 servers total, 2 for production, 2 for testing.

The file locations for the database and datastore are in different locations on each server.  
I've backed up the production SQL 2005 database using the built-in SQL tools, and have also restored to the test SQL server, choosing to "overwrite the existing database" successfully.

I am supposed to login to the application/admin test server and attach to this test database that I've just restored, however it doesn't seem to be taking my credentials.  What I'm asked for is:

Database Server Name:  <testserverdb>
Database Name: <does THIS need to be exactly the same name as the prod. db??>
SQL username:  <production DB uses windows auth., so I'm putting in my domain admin username>
SQL password:  <putting in password for domain admin above>

I'm thinking either I have something wrong with the name of the database (if that matters), or it looking for a specific account.

When I connect to the SQL Production server using SQL Manager, I am using the same credentials I'm putting in for the restored test DB and can connect fine....

I know this is basic, and I'm missing something very basic, but can't figure it out....Suggestions??  Thanks in advance.
Question by:tenover
  • 8
  • 7
LVL 13

Expert Comment

ID: 35063345
after u restore a db , u need to resync the users of the database.
read this and it might help

and try Use:

exec sp_change_users_login @action= 'AUTO_FIX', @usernamepattern = 'username';

can u connect to the db using sql management studio and using a sysadm account?

LVL 20

Accepted Solution

Mark Brady earned 500 total points
ID: 35063376
When you connect to a DB you need several things.

1: servername (usually localserver) if it is on a local machine and not a dedicated server.
2: username (this is a mysql username and not a domain username)
3: password - once again, mysql specific nothing to do with domains or windows.
4: database name - this is the database you want to connect to so the name is very important.

Do you have phpmyadmin? If not, get it, it is well worth it and free. If so, open it up and look at your databases you have setup in there. From there you can look into the usernames associated with the database and see what privillages you have set.

Yes, database name is very important to get write.

Author Comment

ID: 35063383
Just found the SA password, and that worked.  Thanks guys.

Author Comment

ID: 35073215
Dang...spoke to soon.  SA lets me login and see things in Management Studio, but I still can't attach to the database with a user who is listed as "db_owner" for that database.
LVL 20

Expert Comment

by:Mark Brady
ID: 35074112
That's crazy. Can you create a new user in mysql? If so, create a new user and give them ALL privillages. Then, find the connection script or code in your php and change the username and password. Then you should be able to connect.

Author Comment

ID: 35074510
Do you mean a new user for the database or a new Login?  IT seems that I can create new logins, but NOT new users for this database....
LVL 20

Expert Comment

by:Mark Brady
ID: 35075081
Yes I meant new user for the database. Do you have phpmyadmin? With that you can get a GUI of the database and create power user accounts so you create a user for ALL databases and when you use that username/password combination in your scripts it will allow full access to any of your databases.

If you don't have phpmyadmin and you can get to a mysql consol window you can do it like this

CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'some_password';

Hit enter and when it is created type this:

GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost';

hit enter. Now that username and password combination should be able to access any database in your system. You will need to create a login script and include it with all your php scripts that need access to a database. This is how I do it.

create this file and save it as "constants.php"

<?php // constants.php
Turn all error warnings and displays off for security and on for debugging */
error_reporting(0);  // Uncomment this line and comment out next line before making this script public
date_default_timezone_set('Antarctica/McMurdo'); // you can set your timezone here or comment the line out

define("DB_SERVER", "localhost");
define("DB_USER", "your_username");
define("DB_PASS", "your_password");
define("DB_NAME", "your database name");
mysql_connect(DB_SERVER, DB_USER, DB_PASS);

Now in your php scripts you can add this line:

<?php // index.php - this can be any php script/page
include("constants.php"); // make sure the path is correct

// now you are automatically connected to the database you named in the "constants.php" file

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$mysql_query($sql)or die(mysql_error());
$nums = mysql_num_rows($result);

if($nums > 0) {
// username and password found so go ahead with your code
header("location: index.php");

That's it. The above $sql call was just a quick example to show you can start performing mysql functions as soon as you put the "include()" line into your scripts

I hope this helps

Author Comment

ID: 35076267
I'm not to knowledgable about SQL (yet), and we don't have a DBA, so bear with me.....
After some more troubleshooting, I noticed this:

When the production database was restored to the test server, the main database user (userx) was copied over into the restored database as well, with the appropriate permissions, HOWEVER, on this test server, there is no LOGIN account for this same user (there IS on the production server).  I made a Login account with the same username and password, however when I try to copy the permissions for this login account to include all the databases and also add "db_owner", it errors out saying that this user is already a member of the database and it can't apply the permissions....I'm totally stuck, and need to fix this ASAP.  It seems that the user I need to use is listed as a database user, but cannot logon to SQL.  Does that make sense?

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 20

Expert Comment

by:Mark Brady
ID: 35076833
Not really. If the user is already a database user then they will be able to log onto the database. Can you post your connection script details - block out the password just post how you are trying to connect

Author Comment

ID: 35076911
I'm trying to connect from a front-end web server.  I also tried connecting from the DataSource/ODBC connection GUI in control panel from a different server and got the same thing.....
LVL 20

Expert Comment

by:Mark Brady
ID: 35077052
Do you have access to load php scripts onto the server? If so, i could write you a script and get you to run it. It will list out the current usernames on the database. I will need the name of the host (localhost) or whatever the name is and the name of the database. Can you ftp up to the server?

Author Comment

ID: 35077288
I can't.  I can SEE the list of users for the database in SQL Manager though, there's only a few.  LEt me ask you this:  Does the user who is trying to connect to this database ALSO need to be a named user for the master database?  Because right now, that's the only difference I can see....

Author Comment

ID: 35077395
If I login with the same user and password to the production server using an ODBC connection, it works just fine.
LVL 20

Expert Comment

by:Mark Brady
ID: 35077447
I'm lost on this one. you simply have no access to even get in the back door. No phpmyadmin, no mysql terminal access and no way to upload web pages. There is no way i can help you except to say that at this point, I would save the databse filles and reinstall the apache server and mysql. With a new installation you will be prompted for a root password and that will get you into ANY database. You will also be able to install phpmyadmin and you won't have these problems any more. If it is only a test server I would perhaps think about doing that. Sorry I'm out of ideas

Author Comment

ID: 35077456
I can access all the database properties in SQL Manager though....nothing I can do there??
LVL 20

Expert Comment

by:Mark Brady
ID: 35077516
I don't know I've never used an SQL manager. sorry

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
In this article I will describe the Copy Database Wizard 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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now