July 9, 2014 at 4:46 am
Hi,
Due to a previous (mis)configuration, i need to grant read\write permission on a share from a MSSQL DB User.
The SQL user will launch t-sql queries on demand and they cannot be scheduled.
I've created a credential object in SSMS, configured it with the correct AD user and mapped it to the MSSQL DB user.
Now, if i execute a simple t-sql backup:
BACKUP DATABASE [DB] TO DISK = N'\\IP.ADD.RE.SS\share\DB.bak' WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
I get an access denied error, monitoring the sqlserv.exe process via procmon, i see that the Sql Server process is not impersonating the AD user configured in the credential, it still try to access using the local machine account .
Thanks in advance.
Stefano
July 10, 2014 at 5:53 pm
Stefano:
Backups are carried out using the SQL Server Service Account. In order to save to a UNC path, you have to use an AD user as the logon for the Service Account, and that user has to have been granted access to that UNC path. If the local service account is used, it will not have access to anything not connected to the server (SAN, C: drive, etc.).
Try changing the logon for the Service Account and see if it works out. BTW - in order to change the Service Account login, you have to bounce the service after changing it for it to take affect. Make sure to take this requirement into account when you plan how and when to carry this out.
Hope it helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply