Access denied two instance but not named instance on same server

Question

Post reply

Access denied two instance but not named instance on same server

Isabelle2378

SSCertifiable

Points: 6174
More actions
May 16, 2011 at 3:22 pm

#238371

Hi,
I have a server with 3 instances on a SQL Server 2008 R2 and only 1 instance can successfully create a database to the I:\SQL_DATA. All three instances are run by the same domain account so I'm not sure why when connected to the one named instance, I can create a database and i'm able to see the directories below the I:\SQL_DATA dir but when I connect to the other two instances, I get the error
CREATE DATABASE failed. Some files could not be created. (Access is denied) while attempting to open or create the physical file 'I:\SQL_DATA\test.mdf'. (Microsoft SQL Server, Error: 1802)
What else contols permissions on the physical file other than the domain account that is running the SQL service? If one instance is able to successfully create a database to this directory and it is the same account that is running the other two instances, what else could cause this error?
Thanks!
Bea Isabelle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

What is the version of the operating system?

K. Brian Kelley
@kbriankelley

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Hi,

The server is running Windows Server 2008R2 Standard.

Thanks.

Thanks!
Bea Isabelle

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

The reason is that with SQL Server 2008 and higher and Windows Server 2008 and higher, access to resources is secured via a SID tied to the SQL Server service, not the user account that SQL Server is running under. This service isolation was first introduced in Windows Vista and is in all subsequent operating systems.

The easiest solution is to have separate directories for each server. Otherwise, you'll be constantly fighting these permission issues.

K. Brian Kelley
@kbriankelley

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 4

Hi Brian

Thank you so much for that information! I created seperate directories for each instance and it worked! I have another server with two instances using the same drive and never ran accross this issue before but it is running server 2008 with SQL 2005.

Thanks again! 😀

Thanks!
Bea Isabelle