Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to login using sqlcmd Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 4:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 6, 2014 6:57 PM
Points: 39, 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
Post #1490581
Posted Monday, September 2, 2013 6:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,485, Visits: 3,033
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’! **
Post #1490600
Posted Monday, September 2, 2013 7:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:19 PM
Points: 825, Visits: 756
Either you specify the password at the prompt, or you use the -P option.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490615
Posted Monday, September 2, 2013 9:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 6, 2014 6:57 PM
Points: 39, 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!
Post #1490739
Posted Monday, September 2, 2013 11:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,485, Visits: 3,033
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’! **
Post #1490755
Posted Tuesday, September 3, 2013 12:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,485, Visits: 3,033
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’! **
Post #1490757
Posted Tuesday, September 3, 2013 2:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 6, 2014 6:57 PM
Points: 39, 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
Post #1490816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse