Cannot access SQL server database after moving server to new domain

Mark Rohrbeck
Mark Rohrbeck used Ask the Experts™
on
Hi all,

I just moved a server that has a document management program installed on it to a new domain. Since moving it I cannot access the database. I changed the service login credentials from the old administrator to the new one. When I open SQL Server management studio and choose windows authentication it opens but when I browse down to databases and try expand the name of my database, it says expanding for a second then errors with: The database KS-AFPG DATA is not accessible. (ObjectExplorer) below is the full error. PLEASE HELP!

===================================

The database KS-AFPG DATA is not accessible. (ObjectExplorer)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
"Since moving it I cannot access the database."

Can you (or someone else that has sufficient credentials) connect to that SQL Server by its ip in SQL SSMS and see what user Databases are accessible?
Mark RohrbeckOwner - Celera IT Services

Author

Commented:
When I open SSMS I am asked for the following info:

Server type: database engine
Server name: servername\databasename
Authentication: windows authentication

I enter the info and click connect. After that I can access all the databases including security\users but not the one I need. Can I add a user somehow?
Mark RohrbeckOwner - Celera IT Services

Author

Commented:
Sorry I mean I can access things like system databases \ master \ security \ users. I know very little about SQL as you can probably tell
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

lcohanDatabase Analyst

Commented:
Can you see the database "KS-AFPG DATA" listed under the Databases on that server after rebuild? If yes, what is its status and try to access/see any objects data under "KS-AFPG DATA"
Mark RohrbeckOwner - Celera IT Services

Author

Commented:
I can see the database  "KS-AFPG DATA" but when I try click on it it gives the error
Mark RohrbeckOwner - Celera IT Services

Author

Commented:
Anyone have any ideas?
lcohanDatabase Analyst

Commented:
"Authentication: windows authentication"
Does that user you mention above has access to the SQL Database
"KS-AFPG DATA" ??
Can you try use another SQL login that you know FOR SURE it can access "KS-AFPG DATA" db?

Unless in SSMS there is a (off-line) or anything else besides the database name "KS-AFPG DATA" its only SQL permissions preventing you from accessing that DB.
Mark RohrbeckOwner - Celera IT Services

Author

Commented:
No and that is the issue. Before this happened all was working fine when the server name was kadaysv02.ks-afpg.local it was a member server in the domain. The SQL server was setup with windows authentication and would use the domain admin account for access. Since then the computer got removed from that domain and is now on a new domain so that old admin account is no longer the one being used when the windows authentication is checked. So you are correct it is SQL permissions preventing me from accessing the database. I just dont know how to correct the issue. I can try join the server back to the old domain so I can access the database but how would that help me get it over to the new domain? How do I grant the new domain admin account access to this database?
lcohanDatabase Analyst

Commented:
You need to get some IT people attention to make sure that "...the domain admin account for access." has access to the new SQL server on the new domain including firewalls, etc.
lcohanDatabase Analyst

Commented:
"How do I grant the new domain admin account access to this database?" I think is more in line with what I just said - someone there at your end must allow NT user DomainA\sql_user to have access to DomainB and access the database server on that domain. Did you checked in SQL Security at both Server and DB level that this NT login - DomainA\sql_user - has sufficient rights to perform all required tasks?
Owner - Celera IT Services
Commented:
I was looking for specific steps on how to do this. I ended up figuring it out. I had to join the server back to the old domain, open SSME, navigate to the users section, add a user for newdomain\user, and give it the same permissions as the old admin account had.
Mark RohrbeckOwner - Celera IT Services

Author

Commented:
Has specific steps on how to solve the problem

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial