I have a stored procedure under SQL Server 2012 Standard which makes calls to sqlcmd through xp_cmdshell in order to execute script files against the database. This always worked fine without passing credentials and without the -E option. Now, under the current version of SQLExpress, I get an error saying "Authentication failed for 'mydomain\mymachine$'" when calling sqlcmd from the stored procedure. It seems that under SQL 2012, sqlcmd is called in the context of either NT Service\MSSQLSERVER while in SQLExpress 2019, it is called in the context of the local machine, although the service is running under NT Service\MSSQL$SQLExpress. Adding the -E switch to sqlcmd does not help, creating a login for the local machine account doesn't help either. Any idea how I can get this to work without having to specify an account and a password for sqlcmd?
1 Answers
0
votes
I forgot to specify the server instance with the -S switch. On the previous server, there was only a default instance of sql server, so this was never an issue. But on my test machine, there is a default instance + an instance of SQLExpress. So sqlcmd was trying to connect to the local standard instance instead of the SQLExpress instance.
