Mystery Case Access denied

In the morning one of the developers of the corporate application contacted the support service. He could not make a copy from the MS SQL Server database, and asked to find out the cause of the error.

The first thing to start with is to check the error for reproducibility.
Let's try to make a copy with the command:

BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY 

Indeed, an attempt to make a copy ended in an error:

')
What could cause such a problem?

SQL Server runs from the built-in “Network Service” account

Just in case, we check the resolution of the name of the FS1 server using the short name and the FQDN. Both names are resolved and, importantly, point to the same server. Open the network folder, check permissions on NTFS and Share Permissions. All right, the SQL1 server account has write permission.

Maybe problems with NTLM, Kerberos? Let's try to make a backup using the FQDN server.

 BACKUP DATABASE [SDB] TO DISK=N'\\FS1.contoso.test\Backup\sdb_full.bak' WITH COPY_ONLY 

Interesting. Using FQDN, the backup was successfully created. What does it mean? Is that the situation has become even more confusing.

SQL Server cannot be restarted during working hours. Staying in the night would not want to.

When nothing is clear, the administrator’s best friend is Wireshark or Microsoft Network Monitor. If you take a good dump, you can either figure it out or get confused.

Putting on a responsible Microsoft Network Monitor server is a theoretically safe event, but life so often makes adjustments to the safest undertakings.

It is impossible to reboot, it is undesirable to put the monitor. Then use the Windows Event Tracing service .

Enable tracing:

 netsh trace start persistent=yes capture=yes tracefile=c:\temp\trace.etl 

Repeated backup command several times:

 BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY BACKUP DATABASE [SDB] TO DISK=N'\\FS1\Backup\sdb_full.bak' WITH COPY_ONLY 

Stop tracing:

 netsh trace stop 

Open the file in Microsoft Network Monitor on the administrator's workstation:


Each time you try to make a copy, the KDC_ERR_PREAUTH_REQUIRED event appears with a mysterious user DBAdmin. This is not an employee, administrator account, it does not start SQL Server.
KDC_ERR_PREAUTH_REQUIRED means that the credentials are incorrect.

But the backup is performed in the context of the “MS SQL Server” service, and it is launched under the “Network Service”. And here is DBAdmin?

In Windows, there is a “Credentials Manager”, also known as the “Credentials Manager,” which allows you to save credentials for various network resources. It can be called with the command “control userpasswords2” or “netplwiz”:


Let's check whether the context of the computer account "SQL1 \ Network Service" saved alternative credentials for the server FS1.

To start the process on behalf of another user, we use psexec.

If you run psexec with the "-s" key, we will get into the context of "Local System". It will not work.

In order to get into the context of the “Network Service” run the utility with the following keys:

 psExec.exe -i -u “nt authority\network service” cmd.exe 

Check if the Access Denied error is repeated in the context of the “Network Service” when accessing the FS1 server:


The error is playing.

Check the saved credentials. Run "control userpasswords2" without witchcraft with Explorer will not work. Yes, and it is not necessary, to work with "Credentials Manager" from the command line, there is a cmdkey.exe utility.

In order to display the saved credentials, run the command:

 cmdkey /list 

No saved credentials found. More interesting.

So, what we know at the moment:

1. In the context of the computer account "SQL1 \ Network Service" when accessing the SMB protocol to the FS1 server, an Access Denied error is returned
2. When accessing the server via FQDN FS1.contoso.test, the error is not returned
3. Access to the FS1 server is performed using the DBAdmin account, which is not used anywhere else explicitly.
4. In the context of "SQL1 \ Network Service" credentials manager credentials are not saved

Wait, but you can save credentials not only in the Credentials Manager, but also in the memory of the Lanman Workstation service .

If you connect the drive with the / savecred option, the credentials will be saved in the Credentials Manager:

 net use \\FS1\Backup /persistent:yes /savecred 

If you omit the / savecred parameter, the credentials will be stored in the service’s memory until a reboot

 net use \\FS1\Backup /persistent:yes /user:DBAdmin 

Check if we have saved connections:

 net use 

There is! Now it is clear why an error was returned when accessing FS1, but not to FS1.contoso.test.

Delete saved connections:

 net use * /delete 

Check backups:


Problem solved.

And what was the matter? The cause of the error is very nontrivial. A network drive under DBAdmin user was connected inside the corporate application on behalf of SQL Server, which was not disconnected due to an error in the application. After some time, the DBAdmin user probably changed the password, or the server was restarted. And here he is, the mysterious Access denied!

What conclusions can be drawn for yourself?

1. When you perform a SQL Server backup, the network resources are accessed on behalf of the SQL Server service account, and not from the user running the BACKUP DATABASE command. Keep this in mind when setting permissions.
2. Always remove additional full backups with the WITH COPY_ONLY key. SQL Server marks data pages that were modified after a full backup, and only modified pages fall into a differential copy. It is logical that after each full backup, the state of the pages is cleared. The key allows you not to clear the page mark, and the sequence will not be broken.
3. In case of an “Access denied” error, it will not be superfluous to check whether the error repeats itself and by the host name, by FQDN, by IP address.
4. You can get into the security context of the account you need by running psexec with the -U switch.
5. The cmdkey utility is used to display credentials from the key storage service.
6. To display the saved connected network connections, use the net use command.

Thanks for attention.

Thanks ildarz , sabin for informative comments, removing inaccuracies and hints.