[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Need help connecting to a restored SQL database

Posted on 2011-03-07
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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?

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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