SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to login using sqlcmd


How to login using sqlcmd

Author
Message
mssqlnoob
mssqlnoob
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 144
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
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8854 Visits: 3718
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
Erland Sommarskog
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5432 Visits: 875
Either you specify the password at the prompt, or you use the -P option.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
mssqlnoob
mssqlnoob
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 144
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
HanShi
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8854 Visits: 3718
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
HanShi
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8854 Visits: 3718
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
mssqlnoob
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 144
after changing security to sql server and windows authentication and restarting the server,

I was able to login using either sa or schemauser.

thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search