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

Sql Server Login Expand / Collapse
Author
Message
Posted Sunday, February 23, 2014 4:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 189, Visits: 682
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.
Post #1544322
Posted Sunday, February 23, 2014 5:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 5,974, Visits: 12,874
please post the exact error you get.

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

Post #1544330
Posted Sunday, February 23, 2014 5:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 189, Visits: 682
Login failed for user. error 18456 when the user is not sysadmin. If I grant the user sysadmin previleges it's working fine.
Post #1544332
Posted Monday, February 24, 2014 3:14 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 5,974, Visits: 12,874
that error will come with a value for 'state', what is that equal to, and what message does SSMS give you?

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

Post #1544397
Posted Monday, February 24, 2014 3:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 1,306, Visits: 2,468
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
Post #1544409
Posted Monday, February 24, 2014 4:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 189, Visits: 682
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.
Post #1544440
Posted Monday, February 24, 2014 5:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1544450
Posted Monday, February 24, 2014 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 189, Visits: 682
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.
Post #1544480
Posted Monday, February 24, 2014 7:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
Local machine and server on the same domain?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1544494
Posted Monday, February 24, 2014 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 189, Visits: 682
Yes.

I tried the following and it worked.

GRANT CONNECT SQL TO [Testuser]
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [TestUser]
Post #1544507
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse