Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11

    Unanswered: Unable to connect to sql server via command line with sa user

    Hello
    I am trying to connect to SQL server 2008 express db from the command line

    Logged on to the server and i enter this command

    Code:
    sqlcmd -Usa -Ppassword -SSBS2011
    It gives this error:

    Msg 18452, Level 14, State 1, Server SBS2011, Line 1
    Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.


    I am able to connect to the Management Studio as the user sa with SQL Server Authentication selected

    I have looked at the Security Properties of the server in the Management Studio and it is set to Windows Authentication and SQL Authentication

    A bit of background info is that about 7 years ago i created a DTS package for this customer when they were running SQL server 2000
    I gave them a batch file that would run DTS from a user PC and it would launch the DTS to import a file
    Their IT company upgraded them to SQL Server 2008 recently and this process will not work
    So they asked me to take a look and the first thing i tried was an osql command to connect and then the sqlcmd line above to connect

    Part of the reason it will not work is that the command line in this batch file is

    Code:
    dtsrun /S SBS2011/SQLEXPRESS /U sa /P password /N AccountsSalesTransfer
    So if it cannot connect as sa from the command line on the server, then it will not connect from a batch file on a user pc

    Apologies in advance if this is a rambling post but i have not looked at SQL server at all for about 5 years and am trying to get my head back into that space

    Any help or advice on this would be much appreciated

    Best regards and thanks

    Patrick

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One clarification, for the sqlcmd command, should there be an instance name? All I see is the server name. Could this imply a second instance of SQL Server on that machine?

  3. #3
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    It could be that reason alright. There is only 1 instance installed on the server

    So i guess my command should have been

    Code:
    sqlcmd -Usa -Ppassword -SSBS2011/SQLEXPRESS
    where SBS2011/SQLEXPRESS represents machine_name/instance_name

    Thanks i can check it out

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The error does not match up very well. If there was no instance, you would have gotten a "server not found or access denied" error. Instead, you got an acknowledgement from the server that sa is not trusted.

  5. #5
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    When i try the command like this on my own system
    Code:
    sqlcmd -Usa -Ppassword -SSBS2011\SQLEXPRESS
    i can log in
    But if i omit the instance name i get the same error

    I have not been back on site today but will have the user try it and will post the result

    Thanks
    Last edited by Paco2012; 03-05-13 at 15:25.

  6. #6
    Join Date
    Mar 2013
    Posts
    10
    Sounds like you created a named instance, or maybe that is the default with SQLExpress ?

  7. #7
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    Hi, apologies for not replying - i just cant get back to the site to test it again...
    I did not set up the system, but from testing SQL Server 2005 installs the name SQLExpress is the default

  8. #8
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    Hi
    I was eventually able to get back on site and the command
    Code:
    sqlcmd -SSBS2011\SQLExpress -Usa -Ppassword
    So i needed the server_name\instance_name in the command line

    Wood for the trees moment, i think

    Thanks for the help and advice though

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •