Sql Server 2012 can't see 2nd drive

  • ml 66943

    SSChasing Mays

    Points: 619

    I installed Sql Server 2012 Express. Everything went without a hitch until I attempted to create a new database and place it on a second drive (E:). The location window displays C: and an external drive (Z:), but not the E: drive where I wish to place the database. Permissions show full control, but Sql Server won't display it.

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    USB attached disk?

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • ml 66943

    SSChasing Mays

    Points: 619

    Yes, external drive is a USB.

  • Geoff A

    SSChampion

    Points: 11407

    you could try this;

    DBCC TraceOn(1807);

    GO

    that will enable using UNC paths in SQL.

    then create a folder on your E: drive called SQL_DATA

    share the folder

    then try to create your database with UNC path.

    Use master;

    GO

    CREATE DATABASE [test]

    ON PRIMARY (

    NAME = N'test',

    FILENAME = N'\\SQL_DATA\test.mdf' ,

    SIZE = 2048KB ,

    FILEGROWTH = 1024KB

    )

    LOG ON (

    NAME = N'test_log',

    FILENAME = N'\\SQL_DATA\test_log.ldf' ,

    SIZE = 1024KB ,

    FILEGROWTH = 10%

    )

    GO

  • ml 66943

    SSChasing Mays

    Points: 619

    Error response: Directory lookup for the file "E:\SQL_DATA\test.mdf" failed with the operating system error 5(Access is denied.)

  • Geoff A

    SSChampion

    Points: 11407

    E:\SQL_DATA is not a share.

    make sure your SQL_DATA share is accesable to the service account that is running SQL.

  • Lowell

    SSC Guru

    Points: 323377

    access denied is due to the account running SQL server.

    To access anything outside of actual databases, SQL uses a different account than you might think it should.

    you will probably want to change the account.

    SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

    or if the above was blank, the account in services:

    That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

    As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

    Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.

    you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ml 66943

    SSChasing Mays

    Points: 619

    Thanks for your insight, but you are getting a bit ahead of my knowledge base.

    I installed SQLEXPRESS using Windows Authentication. This is a simple, 3-computer network that doesn’t use domains. I have little need for, or desire to learn all of the security intricacies of Microsoft. I simply want to put my database on a second drive.

    Sharing is enabled for both the login user ID & admin user ID

    Explicit sharing permissions added for SQLEXPRESS and SQLBROWSER

    Explicit security permissions added for SQLEXPRESS with full control

    All user accounts have full control access to the E: drive.

    Is there a rational, simple-to-understand tutorial on all of this, or do I have to continue to thrash through bits and pieces to enable use of my computer?

  • ml 66943

    SSChasing Mays

    Points: 619

    Problem was fixed by going to Control Panel/Services/SQL Server (SQLEXPRESS) Properties and changing Logon to Local System Account.

  • Paul Kalinski

    Old Hand

    Points: 303

    I have a similar problem with a brand new server 2012 and sql server 2012 installation using the standard sql version.

    I can only restore/attach a db to my D and L drives if I run sql service as SYSTEM.

    If I assign a domain user to sql service I can't see any other drives other than C, and can't attach a db with error below:

    Msg 5133, Level 16, State 1, Line 1

    Directory lookup for the file "D:\MSSQL\Data\MyDatabase_data.mdf" failed with the operating system error 5(Access is denied.).

    I would like to run it as a domain user to I can run backups directly to a network share.

    I run Management Studio as Administrator.

    Full Permissions for the domain account have been set explicitly on both drives and inherited down.

    If I set explicit permissions on just the folders in the drives (D:\MSSQL\Data and L:\MSSQL\Logs) I still get the same error.

    This also happens if the domain account used is a domain admin.

    Has anyone else been able to get this to work?

  • knightdragon89

    SSC Journeyman

    Points: 97

    I'm having the same problem.

    Server 2012 standard edition

    SQL Server 2012 enterprise edition

    a domain service account, which is also in the server's local administrator group

    the domain service account is a sysadmin in MSSQL

    SQL Server is in mixed mode

    when I run the MSSQLSERVER service as the domain service account, I can only see the C drive from within MSSQL

    when I run the service as Local System account, I can see all of my drives

    It 'seems' that things run as local system account, but I "need" it to run as a domain service account so I can use ALWAYSON.

    Any thoughts or ideas? I'm at a loss.

  • machine2473

    SSC Veteran

    Points: 220

    Same issue here.

    SQL 2012 Enterprise w/ SP1, installed and running on Windows Server 2012 Standard.

    The per-service SIDs were added with full control to the drives/folders set aside for user databases but the instance is still unable to "see" the non-C: drives.

  • dmccosh

    SSC Enthusiast

    Points: 180

    I'm Having the exact same problem too!!

    As a test I installed SQL 2012 on 2008R2 without issue (using the same "vanilla install" & service accounts (domain))

    Both machines are VM's (running on ESX5)

    Trying to figure out where the source of the problem is - looks more like a Windows Server 2012 or Hypervisor issue....

    Anyone experiencing anything similar?

  • machine2473

    SSC Veteran

    Points: 220

    Ours is on a Windows Server 2012 VM also.

  • dmccosh

    SSC Enthusiast

    Points: 180

    Group policy was the problem in our case, try putting your win server in an Ou that inherits no group policy, we were having problems with "audit object access"

Viewing 15 posts - 1 through 15 (of 25 total)

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