SharePoint Database Creation Failure

loginspermissionsSecuritysharepoint

I wonder if this question will need to be moved to the SharePoint SE site, but I believe this could be a simple a SQL Server security fix.

A little back story:
Back in April we upgraded our SharePoint SQL Server backend (mirrored) from SQL 2005 to SQL 2014. We did this by way of mirroring from SQL 2005 (primary) to SQL 2014 (mirror). I don't believe the security settings and logins were extracted from the old server and placed in the new. I'll have to reach out to my infrastructure department to see if the server has been completely decommissioned.

My SharePoint Admin is trying to create a new site from the SharePoint Central Admin Site (SharePoint 2010), but gets the following error.

Error "Directory lookup for the file "D:\Data\NewSiteDB.mdf" failed
with the operating system error 2(The system cannot find the file
specified.). CREATE DATABASE failed. Some file names listed could not
be created."

He told me that he used to be able to use his Active Directory account or a SQL Server SysAdmin account to create new sites, now he can't. I'm pretty sure that his AD account was not an admin of any kind on the SQL Servers regarding the SQL 2005 configuration. Further, he stated that he used a SQL Server SysAdmin account (in the SQL 2014 configuration) but it still failed.

What can I do if the old server is decomm'd?
If the server isn't decomm'd, and I can access, how do I extract all the logins, permissions, SID's, etc.?
Is it possible to create a new mapping from the SharePoint Central Admin site to a new SQL/Windows login?

Let me know if you need any other info and thank you in advance for your help!

Best Answer

This is an operating system error, reported to the SQL Server service. The SQL Server service account, which creates the files, needs to have at least change permissions on the d:\data folder and it needs to exist.

Related Solutions

Sql-server – SQL Server Permission Denied Error When I’m DBOwner

From what I see you created a server login with server roles (DBCreator and SecurityAdmin are server roles). But this doesn't mean it already has privileges on an existing database.

What you need to do is to create a user in that database and map it to the login:

use your_db
create user x for login y
exec sp_addrolemember 'db_owner', 'x'

This will assign your new user the database role db_owner to the database. Now you should be able to create tables with the login. You can try also other roles, if you don't need to be the db owner.

Sql-server – Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

Add BOTH Domain\Servername$ login and NT AUTHORITY\Network Service login to SQL Server instance giving you above problem and give both logins the right permissions.

If you are working in a Spanish environment or another language try adding NT AUTHORITY\Servicio de Red or whatever the equivalent is in your language.

For steps on adding logins see links: http://support.microsoft.com/kb/889646/en-us and http://www.c-sharpcorner.com/UploadFile/specialhost/how-to-fix-login-failed-for-user-nt-authority-iusrs-in-share/ and https://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename

If you have a SQL Server Instance with logins that have been working for you when accessed by application pools and the like, you might want to check which logins are not in the troublesome SQL Server instance and that you might need.

Other comments:

Ok, even though the error said: Login Failed for user 'Domain\Servername$' and I added that login to the problematic SQL Server instance per what I'd seen described on the internet, it seems that the login I ALSO needed to add was NT AUTHORITY\Network Service.

I basically decided to add any logins that were in my normal SQL Server instance that I was using for the regular SharePoint databases and that weren't in the SQL Server instance I was using for SharePoint Reporting Services to the latter. Something else that tripped me up when I was looking originally to add the NT AUTHORITY\Network Service login was that I couldn't find it. It turns out I had to add it in Spanish as NT AUTHORITY\Servicio de red, which is wierd because the app pool has its Identity in English. I guess this happens when some systems have English as default language and some others have Spanish

Related Question