SQL Clone
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
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7221 Visits: 5011
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
ml 66943
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 4
Yes, external drive is a USB.
Geoff A
Geoff A
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2344 Visits: 1808
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
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2344 Visits: 1808
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
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67721 Visits: 40894
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
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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