How to login using sqlcmd

Question

Post reply

How to login using sqlcmd

mssqlnoob

Ten Centuries

Points: 1245
More actions
September 2, 2013 at 4:25 am

#281948

Hi,
my rdbms version :2012 sqlexpress
I created the login using the below commands:
SETUSER
GO
USE master
GO
IF EXISTS( SELECT * FROM sysdatabases WHERE name='permissionsDB' )
DROP DATABASE permissionsDB
GO
IF EXISTS( SELECT * FROM sys.sql_logins WHERE name='schemaUser' )
DROP LOGIN schemaUser
GO
CREATE DATABASE permissionsDB
GO
CREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDB
GO
GRANT CONNECT SQL TO schemaUser
GO
USE permissionsDB
GO
CREATE USER schemaUser WITH DEFAULT_SCHEMA=dbo
GO
what is the correct option to login as schemaUser using sqlcmd?
http://technet.microsoft.com/en-us/library/ms162773.aspx
I try the following it fails
C:\Users\user>sqlcmd -S user-pc\sqlexpress -U schemaUser
Password: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed
for user 'schemaUser'..
C:\Users\user>sqlcmd -S user-pc\sqlexpress -U schemaUser
Password: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed
for user 'schemaUser'..
any assistance is really appreciated!
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

The code you have posted is working correct.

Are you able to login with the credentials using Manamgement Studio?

Does the SQL Error logging contain more information regarding these login failures? Please post all error messages...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Either you specify the password at the prompt, or you use the -P option.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

mssqlnoob Ten Centuries Points: 1245 More actions · Answer 3

Are you able to login with the credentials using Manamgement Studio?

Yes I'm able to login to Manamgement Studio using windows authentication as an admin

But I'm not able to login as sa

when i execute the following statement,

CREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDB

Is it creating a sql server account or windows account?

error is as follow:

TITLE: Connect to Server

------------------------------

Cannot connect to USER-PC\SQLEXPRESS.

------------------------------

ADDITIONAL INFORMATION:

Login failed for user 'schemaUser'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Thanks a lot!

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

With your CREATE LOGIN statement a SQL account is created.

If you can login as administrator but you can't login as 'sa' then it looks like the authentication mode of the instance is set to "Windows Authentication mode". This prevents the use of SQL accounts.

If it is needed (according to your security policy) you can change this setting using SSMS. Navigate to the 'security' tab of the properties of the instance.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33507 More actions · Answer 5

If you want to use Windows authentication with SQLCMD you can use the -E switch. This sets the security to 'integrated mode' and uses the current Windows account. You need to omit the -U and -P switch.

SQLCMD -S {server\instance} -d {dbname} -E

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

mssqlnoob Ten Centuries Points: 1245 More actions · Answer 6

after changing security to sql server and windows authentication and restarting the server,

I was able to login using either sa or schemauser.

thanks