Solved

Need help connecting to a restored SQL database

Posted on 2011-03-07
16
298 Views
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.
0
Comment
Question by:tenover
  • 8
  • 7
16 Comments
 
LVL 13

Expert Comment

by:Wizilling
Comment Utility
after u restore a db , u need to resync the users of the database.
read this and it might help

http://msdn.microsoft.com/en-us/library/ms174378.aspx

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?




0
 
LVL 20

Accepted Solution

by:
Mark Brady earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:tenover
Comment Utility
Just found the SA password, and that worked.  Thanks guys.
0
 

Author Comment

by:tenover
Comment Utility
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.
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
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.
0
 

Author Comment

by:tenover
Comment Utility
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....
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
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 */
ini_set("display_errors","off");
error_reporting(0);  // Uncomment this line and comment out next line before making this script public
//error_reporting(E_ALL);
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);
mysql_select_db(DB_NAME);
?>

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
}else{
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
0
 

Author Comment

by:tenover
Comment Utility
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?

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
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
0
 

Author Comment

by:tenover
Comment Utility
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.....
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
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?
0
 

Author Comment

by:tenover
Comment Utility
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....
0
 

Author Comment

by:tenover
Comment Utility
FYI-
If I login with the same user and password to the production server using an ODBC connection, it works just fine.
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
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
0
 

Author Comment

by:tenover
Comment Utility
I can access all the database properties in SQL Manager though....nothing I can do there??
0
 
LVL 20

Expert Comment

by:Mark Brady
Comment Utility
I don't know I've never used an SQL manager. sorry
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sharepoint 3.0 migration 4 38
ASP SQL Syntax Duplicate Key 7 64
C# Application Local DB Connection String 23 57
SQL JOIN 6 31
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

11 Experts available now in Live!

Get 1:1 Help Now