August 24, 2009 at 11:23 am
I'm totally new to SQL, so please forgive my ignorance.
The OS on our Windows 2008/SQL 2008 server became corrupted and needed to be re-installed. However, the guy that built it originally (who's gone now) created a single partition where he installed the OS and SQL. So, everything was on c:\. The databases are small, so space wasn't an issue. But re-installing the OS destroyed the data on the volume.
Although the OS wouldn't start, I could access the data using a windows install disk and the recovery console. The databases had all been installed into c:\DB\ (Instead of c:\program files\microsoft SQL server\. So, before re-installing the OS, I copied off the entire c:\DB\ folder. I also have backups of the databases made a couple days before the problem. The databases are small, and don't change much, so these backups are fine, if I can get it all working.
For some totally unrelated reasons I don't want to go into right here, I've got some time before I can actually re-install SQL2008 on the production server and restore the DB's to it. I wanted to perform these operations in a test environment for the practice, while I had the time. So I built a domain with the same server names, same domain name, and same admin usernames and passwords as exist on our production domain. I am aware that the SIDs of these computer and user accounts are not the same as the SIDs on the production equivalents.
Here are the steps I performed on the SQL server in the test environment (the exact steps... if it's not listed, I didn't do it!)
1. Installed SQL2008 into the same location (I think) as it existed on the original server. I installed all the options, and have tried it in both Windows Authentication only, and Mixed mode. I believe the original server was Windows Authentication only.
2. After installation of SQL was complete, I shut down all the SQL services and opened a command prompt. From the c:\DB\msssql10.msssqlserver\mssql\binn directory, I started SQL server in single user mode by typing sqlservr.exe -m and hitting enter.
3. I opened another command prompt, and entered the SQL Command utility by typing SQLCMD
4. I restored the master DB from backup by typing Restore Database Master from disk = 'c:\dbbu\master.bak' with replace and hitting enter. It told me that the restore was successful, and it shut down SQL server.
5. I closed the command prompts, and restarted all the sql server services. They all started successfully.
At this point, I opened the MSSMS, hoping to restore the rest of the databases. But when I try to connect, I get the error:
Login Failed for user '\domainname\username', (Microsoft SQL Server, Error: 18456)
If I try to open the SQL Command Utility, I get a similar error.
I'm trying to do this on the server console itself, so I don't think it's a network issue or firewall issue. Still, disabling the firewall had no effect.
I am assuming that this is happening because even though the username and password I'm trying to use to connect are the same, the SIDs are different than what they are in the production domain. That's my first question... is this the problem?
My second question is; what do I do now? I have read articles about mapping SQL logins to existing users, but I don't know how to do this since I can't connect via Managment Studio or the SQL Command utility.
I am not sure, but I suspect that this problem won't happen when I re-install SQL into the actual production environment, because the domain accounts are all still in place... the one exception being the local computer account because that account will have a different sid due to the re-installation of the OS. But still, if for no other reason than the experience, I'd like to get it working in the test environment if possible. My main goal is to be able to view and document the user permissions and so on that existed on the original server, in case I have to reproduce them manually.
Again, I apologize for my inexperience. Thanks in advance for any assistance.
Art
August 25, 2009 at 8:38 am
do you provide sa password? if yes try logging in using that. If you are able to login , then you will have to fix all the orphan users.
August 25, 2009 at 9:42 am
Thanks Apurva,
I've tried it both ways... windows authentication only, AND mixed mode. Logging in as SA once the master db has been restored gives me the same error as it does when logging in using the domain account.
Art
August 28, 2009 at 9:53 am
I was able to get around this problem in my test environment in the following way:
1. Restored master database in single-user mode as described above... when complete, SQL server shuts down.
2. Restarted SQL again in single-user mode
3. Open MSSMS, and when the login box appears, hit cancel
4. close the object explorer window (with Obj Exp open, MSSMS is trying to make more than one connection to the DB, which doesn't work with single-user mode)
5. connect to the DB as SA user, and select 'New Query'.
6. use the sp_addsrvrrolemember command to add desired domain user account to the sysadmin role. The actual command I used was: EXEC sp_addsrvrolemember 'domainname\username' 'sysadmin';
7. exit MSSMS and restart SQL
8. log into the server as the user that was given sysadmin role. start MSSMS
9. restore the rest of the databases from backup.
This has left me in the position where I can administer the databases, as well as see all the roles and permissions that were assigned originally. I'm guessing that now I can map the SQL logins to the corresponding user accounts in my test domain.
The original SQL server was setup as windows authentication only. I believe that the key to my being able to get this far was to install SQL in mixed mode in my test domain. This allowed me to use the SA login to assign the sysadmin role to another user... I don't think I'd have been able to do this without the option of connected as SA... but I cannot say this with 100% certainty.
Yesterday, I was able to re-install SQL on the production server and restore the databases to it. In the actual production domain, I had none of the issues I experienced in the test domain.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply