Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Server 2012 can't see 2nd drive


Sql Server 2012 can't see 2nd drive

Author
Message
ml 66943
ml 66943
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 4
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 (ESmile. The location window displays C: and an external drive (ZSmile, 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
Henrico Bekker
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 3202
USB attached disk?

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
ml 66943
ml 66943
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 4
Yes, external drive is a USB.
Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1790
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
ml 66943
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 4
Error response: Directory lookup for the file "E:\SQL_DATA\test.mdf" failed with the operating system error 5(Access is denied.)
Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1790
E:\SQL_DATA is not a share.

make sure your SQL_DATA share is accesable to the service account that is running SQL.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38940
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
ml 66943
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 4
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
ml 66943
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 4
Problem was fixed by going to Control Panel/Services/SQL Server (SQLEXPRESS) Properties and changing Logon to Local System Account.
Paul Kalinski
Paul Kalinski
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 73
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search