Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Sql Server 2012 can't see 2nd drive Expand / Collapse
Author
Message
Posted Monday, May 14, 2012 9:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 9:24 AM
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 (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.
Post #1300021
Posted Tuesday, May 15, 2012 12:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,904, Visits: 2,846
USB attached disk?

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1300045
Posted Tuesday, May 15, 2012 8:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 9:24 AM
Points: 5, Visits: 4
Yes, external drive is a USB.
Post #1300260
Posted Tuesday, May 15, 2012 8:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
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

Post #1300270
Posted Tuesday, May 15, 2012 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 9:24 AM
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.)
Post #1300302
Posted Tuesday, May 15, 2012 8:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
E:\SQL_DATA is not a share.

make sure your SQL_DATA share is accesable to the service account that is running SQL.
Post #1300307
Posted Tuesday, May 15, 2012 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1300309
Posted Tuesday, May 15, 2012 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 9:24 AM
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?
Post #1300334
Posted Tuesday, May 15, 2012 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 9:24 AM
Points: 5, Visits: 4
Problem was fixed by going to Control Panel/Services/SQL Server (SQLEXPRESS) Properties and changing Logon to Local System Account.
Post #1300370
Posted Wednesday, October 17, 2012 5:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 21, 2013 10:49 PM
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?
Post #1374087
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse