CODEWITHSUNDEEP
sql-serversqlcmd
Raj
Raj

Reputation: 22956

Connecting to a SQL Server in a network via Command Line and Windows Authentication

From the system where the server is running, I can login using the following command:

sqlcmd -S "ComputerName\InstanceName" -d "DatabaseName" -i "sql.txt" -s"," -o "result.csv"

I want to connect to this server from another machine, So I tried this

sqlcmd -U "UserName" -S "ComputerName\InstanceName" -d "DatabaseName" -i "sql.txt" -s"," -o "result.csv"

"UserName" above is the default user name of the server machine that has administrative privileges. This user account does not have any password.

After issuing that command, it asks for password. Since there is no password, I hit enter. But I get the following error:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'UserName'..

SQLCMD Reference

Upvotes: 1

Views: 24775

Answers (3)

Proxyma
Proxyma

Reputation: 35

The issue is related to differences between SQL and windows authentication. In case of windows authentication e.g. in domain environment with several untrusted domains it is only option to use runas.exe /netonly /user:YourTargetDomain\SQLUser to run SQLCMD or Ssms.exe (SQL management studio) under the proper SQL User.

Both sqlcmd and ssms can use Windows (also domain) authentication without any credentials SQLCMD -E (UI option in Studio) but process should be run as a local or domain user configured for NT Authentification in SQL. Or provide SQL user credentials (configured directly on SQL server e.g. SA) - SQLCMD -U SQLUserName -P SQLUserPassword (the second UI option in the Studio).

Commands below can be used to connect to SQL server configured with Windows (NT) authentication (e.g. lab domain user) from machine in separate untrusted domain (e.g. from office laptop with your office domain account - not configured to access the SQL server).

To run SSMS (it is possible to create a windows desktop shortcut, path to ssms can be different, depends on used version and installation preferences):

C:\Windows\System32\runas.exe /netonly /user:MyLabDomain\SqlUser "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe

To run a query with SQLCMD (in the example, it cleans a table, also can be used as a shortcut):

C:\Windows\System32\runas.exe /netonly /user:MyLabDomain\SqlUser "sqlcmd -S 123.123.12.34 -E -I -Q \"delete FROM TestDB..TestTableToEmpty\""
  • SSMS or SQLCMD should be installed on machine from which you connect to SQL server.
  • After run you should enter passwords for the SQL User.

I suppose, these examples may help somebody.

Upvotes: 0

baron.wang
baron.wang

Reputation: 361

i met this situation:

sqlcmd -S "server" -d "db" -E   -i test.sql

which gives "Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'DOMAIN\administrator'.."

but when i switched to:

sqlcmd -S server -d db -E   -i test.sql

it works fine. so look out for your double quotes. hope this maybe of help.

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294417

-U UserName specifies the name of a SQL Server login, not a Windows user. It seems to me you want to use a Windows user. The proper way to do it is to have a domain, run as a domain user and grant appropriate permissions in SQL to domain group of which you are member. Bare that, you can use runas /netonly /user:ComputerName\UserName sqlcmd -E.

Upvotes: 2

Related Questions