Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 1 down vote favorite

I need some assistance trying to figure out what is going on. We have been running our test SharePoint and test SQL together on the same server and it has been doing ok, but we decided to move SQL to its own server for a number of reasons. My issues though is once I moved the database over to our new SQL Server, SharePoint can no longer connect to it. The browser just shows a empty white page when trying to navigate to any sites on SharePoint. Using Fiddler, I can see that I am also getting a 401 Unauthorized, but not sure why. I used this process for moving the database Moving SharePoint to a different SQL server and here are the troubleshooting steps I have performed so far:

  1. Verified that I could open the databases on the new SQL Server from the old server via SQL Server Management Studio and the command line (sqlcmd).
  2. Successfully pinged new server from old server.
  3. Setup a SQL alias using the server name (even tried the IP). Both the old server and new server were running the database in the default instance

Now I'm thinking that permissions may be causing some kind of issue, but I am having trouble seeing how as I simply restored a backup of the database from the old server to the new server. I'm not sure how to really double check permissions though (as far as I can see everything looks the same when I compare the accounts and permissions in SQL Server Management for both servers).

Both servers are Windows 2008 R2 Standard and have Microsoft SQL Server 2012. The SharePoint server is running SharePoint 2013.

Any help would be greatly appreciated! Thanks!

share|improve this question
Have you tried resetting the Site Collection administration users? – C. Marius Feb 14 at 18:46
1  
There are usually (at least) two accounts that need access to the database, the farm account and the application pool account. First, are these both domain accounts? Second, did the logins for these accounts exist on the new SQL Server before you moved the DBs? – Lou Feb 14 at 18:55
C. Marius: No I have not tried that yet. Interestingly enough I am getting a 404 Page Not Found error when trying to access the Central Admin page. – Tom Feb 14 at 19:04
GemCer: The Farm Account and the Application Pool account are the same domain account. We used a single account to install SharePoint and SQL Server on the old server and then the same account to instal SQL Server on the new server (before moving the databases of course). In fact our SharePoint only has one managed account which SharePoint likes to point out is bad (it's only a test environment though) – Tom Feb 14 at 19:10
I have sometimes needed to delete the farm/app pool account from the database after it is restored and add it again (make sure to give it dbowner permissions). If that doesn't work, you could turn on SQL Profiler and look to see if your SharePoint farm is even hitting the SQL Server. – Lou Feb 14 at 19:24
show 2 more comments

2 Answers

up vote 0 down vote accepted

You can use SQL Profiler to monitor your new SQL Server and help pinpoint what the problem may be. It will show you if you are getting an access denied message or any other relevant information.

share|improve this answer
up vote 0 down vote

Moving the databases to another server and using a SQL alias to point to it is a very good approach. However, you need to copy all the logins from the original SQL Server to the new one.

You can do this by running the sp_help_revlogin stored procedure:

http://support.microsoft.com/kb/918992

This article includes the script to install sp_help_revlogin on your system. Execute it and then copy its output. Run this output in a new query windows on the new server.

The output generated has a lot of lines like this:

CREATE LOGIN [DOMAIN\USERNAME] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

You can can cleanup the output before running it on the new server, you might not want all of these logins to be copied over.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.