Post reply

Sql Server Login

  • February 23, 2014 at 4:45 pm

    #286005

    Hi,

    All the logins (windows and sql logins)on the sql server work when I RDP onto the server but they do not work from the SSMS on my local machine. The only time these logins work from my local machine is when I make the login sysadmin.

    I have never seen something like this. Any ideas?

    Thanks.

  • February 23, 2014 at 5:09 pm

    #1691396

    please post the exact error you get.

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

  • February 23, 2014 at 5:41 pm

    #1691398

    Login failed for user. error 18456 when the user is not sysadmin. If I grant the user sysadmin previleges it's working fine.

  • February 24, 2014 at 3:14 am

    #1691443

    that error will come with a value for 'state', what is that equal to, and what message does SSMS give you?

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

  • February 24, 2014 at 3:45 am

    #1691451

    sql_novice_2007 (2/23/2014)

    Login failed for user. error 18456 when the user is not sysadmin. If I grant the user sysadmin previleges it's working fine.

    Have you checked the default_database settings for this login? Does this login has a corresponding user in its default_database (in case the default_database is a user database & not a system database) & it has appropriate permissions on this database?

    Sujeet Singh

  • February 24, 2014 at 4:42 am

    #1691478

    Yes.I checked all of the above.I can see the logins work from rdp session,but not from the ssms from the local machine.

  • February 24, 2014 at 5:16 am

    #1691488

    Look in the SQL Server error log, what is the exact message which gets recorded for the login failure?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 24, 2014 at 6:57 am

    #1691512

    Following is the error message is error log.

    Login failed for user 'UserTest'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.

    Thanks.

  • February 24, 2014 at 7:11 am

    #1691525

    Local machine and server on the same domain?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 24, 2014 at 7:23 am

    #1691537

    Yes.

    I tried the following and it worked.

    GRANT CONNECT SQL TO [Testuser]

    GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [TestUser]

Viewing 10 posts - 1 through 9 (of 9 total)

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