Granting access to remote location to SQL Server 2012

Question

Granting access to remote location to SQL Server 2012

andrew gothard

SSChampion

Points: 12314
More actions
June 25, 2013 at 6:45 am

#295066

Briefly, what I'm looking to do is set up a job which takes backups locally and then moves them to a server dedicated to holding SQL Server backups.
Now, in previous versions of SQLServer, nice and easy, for Server name Wibble using system account for SQL Server services - create directory on target, share, add Wibble$ - set full control permissions to wibble$, works fine.
With the different approach to default SQL Server services in 2012 - I can't get this working. This will have an account MSSQL$Wibble (for named instance Wibble) - Assigning Wibble$ full control permissions to its directory on the remote backup server doesn't work - the copyover job fails with an access error.
The following suggests it should work as I understand it - but I can't get it to.
"Virtual accounts cannot be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format <domain_name>\<computer_name>$." at http://technet.microsoft.com/en-us/library/ms143504.aspx#Windows
Can anyone who's done this point me in the right direction?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

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

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

Maddave SSCertifiable Points: 6828 More actions · Answer 1

Can you change the SQL server to run under a dedicated account rather then system account? Then you should be able to set up the share to have write access for that account. Alternatively, you could write the backups locally and have a robocopy job to get them to the share - but that would add another point of failure and not guarantee the file is copied.

Lowell SSC Guru Points: 323518 More actions · Answer 2

Maddave (6/28/2013)
Can you change the SQL server to run under a dedicated account rather then system account? Then you should be able to set up the share to have write access for that account. Alternatively, you could write the backups locally and have a robocopy job to get them to the share - but that would add another point of failure and not guarantee the file is copied.

That's exactly what I do in my shop;

we create a domain user \MyDomain\SQLService

The we make SQLService a local admin on the machine(s) that might host a SQL instance, mostly in case we need access to local folders for doing ETL/bulk insert/bcp in/out/backup&restore in unexpected folder paths.

from there, we just assign permissions on various shares on the network to that AD user, and that user is the one that is used as the account running the service.

the automatically created accounts like MSSQL$Wibble are fine until you need access to network shares, then you need to migrate to a domain user.

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!

andrew gothard SSChampion Points: 12314 More actions · Answer 3

It's certainly an option, or even standard local system would do, that's easy enough to assign remote permissions for. I was just wondering if I was missing something obvious - what with the documentation rather strongly suggesting that the same process that works for Local System would work for the accounts generated for SQL server.

Wow - the documentation and the reality failing to match ...

Thanks for the input, much appreciated

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.