Invoke-sqlcmd on remote machine

Question

I am using following code to execute a query on a remote machine.

Invoke-Sqlcmd -ServerInstance $serverInstance -Database $dbName -InputFile $filePath -U "test" -P "testpwd"

But getting an error like

Invoke-Sqlcmd : Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

My database server and my machine are in same domain but both are different machnies. Please help.

Are you using Powershell remoting on remote machine and then executing invoke-sqlcmd? — Chad Miller, Jun 10 '13 at 15:09

score 0 · Answer 1 · answered Mar 17 '14 at 10:39

0

Make sure you have Windows Authentication and SQL Authentication switched on on the database server you are trying to access.!

enter image description here

answered Mar 17 '14 at 10:39

David Aleu

3,862
3
26
48

score 0 · Answer 2 · answered Apr 21 '20 at 00:14

First of all, I am assuming that your $serverInstance variable is an object, instantiated in the following way:

$serverInstance = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLInstanceName)

I believe that by the time you were having this issue you were using IntegratedSecurity = true (the default value). You can set this to false with the following assignment:

$serverInstance.ConnectionContext.LoginSecure=$false;

An explanation on how this solves the error you were having can be found in this answer:

If your SQL Server is on one domain controller and you are trying to connect to it from another domain controller then you will get this error when
IntegratedSecurity = true;
This will happen even if you include a valid SQL Server username and password in your connection string as they will automatically be over-written with your windows login and password. Integrated security means simply - use your windows credentials for login verification to SQL Server. So, if you are logged in to a different domain controller then it will fail. In the case where you are on two different domain controllers then you have no choice but to use
IntegratedSecurity = false;
Now, when Integrated security is false SQL Server will use the SQL Server login and password provided in your connection string. For this to work, the SQL Server instance has to have its authentication mode configured to mixed mode, being, SQL Server and Windows Authentication mode.

To verify or change this setting in SQL Server you can open the SQL Server Management Studio and right-click on your server name and then select Properties. On the pop-up that appears select Security and you will see where to alter this setting if you need to.

Invoke-sqlcmd on remote machine

2 Answers2