T-SQL Tuesday #178 – Recent Technical Issue You Resolved

Deepthi Goguri has our invitation for this month’s T-SQL Tuesday. Deepthi asks for stories on a recent technological problem we fixed. As always, to read the invite post, click on the T-SQL logo.

Access Denied

I was testing database restores to a new server with Copy-DbaDatabase and noticed that I was getting an error. Logs indicated the SQL Server account couldn’t access the server containing database backups (we’ll call this BackupServer2). The issue was odd considering I hadn’t had this problem with my other backup servers.

I connected to the latest SQL Server, opened SSMS, and tried to restore from there. Sure enough, I was presented with the error:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

I tried accessing the path in Windows Explorer for BackupServer02 and it opened successfully. I knew this wasn’t using the same account being used for database restores but at least I knew BackupServer2 was accessible. I copied and pasted the path into SSMS to double check that I didn’t have any typos. SSMS still couldn’t access the backups.

Not Like The Other

I tried connecting to BackupServer01 which had been used recently for backups; I had no problems whatsoever accessing it from SSMS. All of those backups were easily accessible. I compared rights between BackupServer01 and BackupServer02 but found no differences.

Finally, I logged onto BackupServer02 and checked on what users were configured. As it turns out, the user I needed was never added on BackupServer02. It was only added to BackupServer01. That would be a problem. I added the missing user account, and all worked as expected.

Access Granted

I wouldn’t call it the most interesting problem to resolve, but that problem happened to be what I was working on at the time of reading the T-SQL Tuesday invite. It was a relatively easy fix, just a matter of following breadcrumbs.

Thanks for reading!