locked
ODBC Error Connecting to SQL2008 Express vCenter Database instance. SQL Server Error:18456 RRS feed

  • Question

  • Hi,

    I have read through all kinds of data and still cannot find out why I am having this problem.  First a little backgroup.  I have a Server 2008R2 system, that is running VMware vCenter Server software, with the default SQL Express installation of the Database. The instance is VIM_SQLEXP.  When I try and setup ODBC conection, on the How should SQL Server verify the authenicy of the logon ID?  If I select With SQL Server authenitcaiton using a login ID and password enter.  I get the following error

    Connection failed:

    SQLState:'28000'

    SQL Server Error: 18456

    [Microsoft][SQL Server Native Client 10.0][SQL Server] Login failed for user 'sql-svc'

    I have installed SQL management studios, and have verified the the sql-svc account has full admin privileges in the Server Roles, I also when into the User Mapping and made sure that it had Database role membership for all of the database, and all of the available roles. 

    I verified that Shared Memory, Named Pipes, and TCP/IP Protocols were all enabled for VIM_SQLEXP.

    I have looked in this forum for a clue but cannot seem to find what I am missing.  I am able to log in, into windows with the sql-svc account and open up management studios, so I know that the rights are working, and the password is correct. 

    Also when I use with Inegraged windows authentication everything works fine.

    All help is appreciated. 


    Tony DiSalvo

    Thursday, May 3, 2012 7:55 PM

Answers

  • hello Tony

    please read the proposed article with overview about login and users in SQL Server see http://msdn.microsoft.com/en-us/library/ms181127.aspx.

    if you select SQL Server authentication" in this dialog than the connection is done via SQL Server authentication and not via Windows Authentication.

    You've created a login "internal\sql-svc" from an windows login and SQL Server knows that it must use Windows Authentication when passing this user and give therefore an error message if you try to connect via SQL Authentication using this login.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by tdisalvo Monday, May 7, 2012 2:09 PM
    Saturday, May 5, 2012 3:12 PM

All replies

  • to allow to connect to the SQL Server using a SQL Login (Login failed for user 'sql-svc') and not a Windows account you need to enable Mixed Mode Authentication on the SQL Server.

    Start SQL Server Management Studio (SSMS) and connect to the SQL Server with a Windows account which does have sysadmin privilege. In the Object Explorer select the server, right click -> Property -> Security -> Mix Mode Authentication. stop and restart SQL Server.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Thursday, May 3, 2012 9:21 PM
  • Hi Tony,

    Please also pay attention to the format of server name.

    Since your instance is a named instance, please use the “computername\instancename” as the format of the server name.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 4, 2012 9:03 AM
  • hi please refer the given link. It may help you.

    http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2003790

    Friday, May 4, 2012 10:23 AM
  • Daniel,

    That was definitely part of it.  So I went into the SQL Management Studio and Right clicked on the instance and selected properties.  Under Security, as per your instructions, I change Server authentication from Windows Authentication mode to SQL Server and Windows Authentication Mode.  I then rebooted the server and tried again, it still failed.

    While in Management Studio, I found the log files and started looking through the logs.  These errors are more verbose that the popup the I got.  This is where I noticed, prior to making the change on mixed mode the error was as follows:

    "Date        5/4/2012 10:27:24 AM
    Log        SQL Server (Archive #1 - 5/4/2012 10:39:00 AM)
    Source        Logon
    Message
    Login failed for user 'sql-svc'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]"

    Now this confuses me because I was using a windows ID INTERNAL\sql-svc which was created as a sql service account. I am guessing that by selecting the Radio Button"With SQL Server authentication" this uses a different connect string even if you are putting in a windows username and password?"  Is this correct?

    So when I changed the login to Mixed mode the error changed to

    "Source        Logon
    Message
    Login failed for user 'INTERNAL\sql-svc'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>]"

    So this lead me to create a local sql account that we can use to connect to this database.  This works.

    My confusion is in the fact that the SQL database had the users listed under security/logins is it just the fact that when you select that radio button, you have to use a local SQL account to connect?

    Thanks


    Tony DiSalvo

    Friday, May 4, 2012 4:22 PM
  • Now this confuses me because I was using a windows ID INTERNAL\sql-svc which was created as a sql service account. I am guessing that by selecting the Radio Button"With SQL Server authentication" this uses a different connect string even if you are putting in a windows username and password?"  Is this correct?

    So when I changed the login to Mixed mode the error changed to

    "Source        Logon
    Message
    Login failed for user 'INTERNAL\sql-svc'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>]"

    So this lead me to create a local sql account that we can use to connect to this database.  This works.

    My confusion is in the fact that the SQL database had the users listed under security/logins is it just the fact that when you select that radio button, you have to use a local SQL account to connect?

    "With SQL Server Authentication" means to use SQL Login User instead of a Windows Authentication.

    if you select that option an passed "internal\sql-svc" the SQL Server detected that you're trying to pass a windows account (internal\sql-svc) as user name and complains about it.

    to get an overview about login and users in SQL Server see http://msdn.microsoft.com/en-us/library/ms181127.aspx.

    in short form:

    you need a Login (Server->Security->Login) to authenticate against SQL Server and a login is mapped to a user in a database (or multiple db) to access the database.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Friday, May 4, 2012 6:49 PM
  • Daniel,

    Thanks for the reply.  I am still confused because I did this. 

    It works with the vfabric as a local account but it will not work with internal\sql-svc.  I know that I have done this in the past with SQL 2000 without issue.  Did something change?  just currious for a definative answer. 

    Thanks again for all of the help everyone.


    Tony DiSalvo

    Friday, May 4, 2012 7:10 PM
  • you're looking at the database users which are map from a login.

    internal\sql-svc is a Windows account for which you've create a SQL Login (Windows user) and mapped with the same name to a database user in VIM_VCDB database.

    vfabric is a SQ Login (SQL user) and you've mapped with the same name to a database user in VIM_VCDB database.

    Go to the server -> Security -> Login node and open property dialog for the two logins (internal\sql-svc, vfabric) and click on User mapping than you'll see to mapping from SQL Login to the database user for (all) database(s) and which permission it will have.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 4, 2012 7:47 PM
  • Daniel,

    OK, I can see that on the vfabric account that is local the User is listed as vfabric

    And on the Internal\sql-svc It shows the INTERNAL\sql-svc with the same rights.  Still can connect via the vfabric local account only. 


    Tony DiSalvo

    Friday, May 4, 2012 9:28 PM
  • OK, I can see that on the vfabric account that is local the User is listed as vfabric

    And on the Internal\sql-svc It shows the INTERNAL\sql-svc with the same rights.  Still can connect via the vfabric local account only. 

    from an earlier posting I assume that you've select SQL Authentication in your application for accessing SQL Server and your application now tries to access via SQL Authentication instead of Windows Authentication and fails therefore.

    To check that internal\sql-svc can access the database, just log onto your computer using intenal\sql-svc account and start SSMS (SQL Server Management Studio) and select Windows Authentication and connect to the SQL server and check if you can access the VIM_VCDB database


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 4, 2012 9:57 PM
  • It can do this. 

    What I do not understand now is that once I have mapped out the Windows account onto the database, why SQL Server authenitcaiton could not use a windows account that has been brought over.  I could have sworn I did this with SQL 2000


    Tony DiSalvo

    Friday, May 4, 2012 10:01 PM
  • hello Tony

    please read the proposed article with overview about login and users in SQL Server see http://msdn.microsoft.com/en-us/library/ms181127.aspx.

    if you select SQL Server authentication" in this dialog than the connection is done via SQL Server authentication and not via Windows Authentication.

    You've created a login "internal\sql-svc" from an windows login and SQL Server knows that it must use Windows Authentication when passing this user and give therefore an error message if you try to connect via SQL Authentication using this login.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by tdisalvo Monday, May 7, 2012 2:09 PM
    Saturday, May 5, 2012 3:12 PM