Need help connecting to a restored SQL database

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.
Who is Participating?
Mark BradyConnect With a Mentor Principal Data EngineerCommented:
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.
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?

tenoverAuthor Commented:
Just found the SA password, and that worked.  Thanks guys.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

tenoverAuthor Commented:
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.
Mark BradyPrincipal Data EngineerCommented:
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.
tenoverAuthor Commented:
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....
Mark BradyPrincipal Data EngineerCommented:
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
tenoverAuthor Commented:
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?

Mark BradyPrincipal Data EngineerCommented:
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
tenoverAuthor Commented:
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.....
Mark BradyPrincipal Data EngineerCommented:
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?
tenoverAuthor Commented:
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....
tenoverAuthor Commented:
If I login with the same user and password to the production server using an ODBC connection, it works just fine.
Mark BradyPrincipal Data EngineerCommented:
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
tenoverAuthor Commented:
I can access all the database properties in SQL Manager though....nothing I can do there??
Mark BradyPrincipal Data EngineerCommented:
I don't know I've never used an SQL manager. sorry
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.