x

Login Error for Windows Login

Hi.

I am having difficulties getting a user to connect to a database on a sandbox system.

I have the windows user as a server login, defaulting to msdb:

CREATE LOGIN [MyDomain\MyUser] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english]

I created the user in msdb:

CREATE USER [MyDomain\MyUser] FOR LOGIN [MyDomain\MyUser] WITH DEFAULT_SCHEMA=[dbo]

The user is a member of the public database role in the msdb, with permissions to see stuff like sys.tables.

When I try to connect using SSMS, I get an error in the SQL Server Error Log:

  • Error Number: 18456 Severity: 14 State: 11

Which means: Valid login but server access failure: [MSDN Blog][1]

I have checked the following:

  1. Server Properties - Permissions - Connect SQL = True
  2. Database Properties - Permissions - Connect = True

Additional Info:

The users sys, INFORMATION_SCHEMA and guest are disabled. The error occurs for all databases on the server, it is not just for msdb.

If I bump the user to a sysadmin I get straight in, so it must have something to do with the permissions as they are. Where do I look and what have I missed?

[1]: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
more ▼

asked Jul 19, 2010 at 03:45 AM in Default

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

1) Check whether the user(login) has Connect SQL permission on the server

2) Check whether the user (login) is member of the PUBLIC server role, which has by default connect permission on the default End Points in the server.

3) If the login is member of the PUBLIC role, check, that the PUBLIC role has Connect permission on the End Point you are using and connecting to.

Also this problem occurs when connection from one machine or from more machines? I saw somewhere, that disabling simple sharing mode helped some people, then when this was enabled, user was authenticated as guest on the server.
more ▼

answered Jul 19, 2010 at 06:30 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Hi Pavel,

1) Yes 2) Yes 3) Yes, as far as I can tell. I have never touched Endpoints before. Where/how could I check that?

The problem occurs for more than one user and machine.

I have also tried adding the AD-Group Domain Users and granting it the permissions, to no avail.
Jul 19, 2010 at 06:48 AM WilliamD

In SSMS: Endpoints you will find under \Server Objects\Endpoints\System Endpoints

Permissions on them you can find in the Logins and Server roles permission. Select properties of eg PUBLIC role, under permissions you should see the permissions for the Endpoints

Are you encountering the problem only for some users or all users? From what OS are you encountering the problems?
Jul 19, 2010 at 07:00 AM Pavel Pawlowski

The Public role was missing the CONNECT permission on the Endpoints.

Good catch! Thanks
Jul 19, 2010 at 07:10 AM WilliamD
Good, that is solved. :-)
Jul 19, 2010 at 07:20 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

Enable the guest account

I think the problem is with your guest users,the guest username is a member of the public role. After the guest user has been added to a database, any individual with a valid SQL Server login regardless of security mode can access the database as the guest user. A guest user works as follows:

SQL Server checks to see whether the login ID has a valid username or alias assigned. If so, SQL Server grants the user access to the database as the username or aliases.

If not, SQL Server checks to see whether a guest username exists. If so, the login ID is granted access to the database as guest. If the guest account does not exist, SQL Server denies access to the database.

more ▼

answered Jul 19, 2010 at 04:05 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

Hi Anuj,

I activated guest in msdb, but this did not help.

If I try setting the default DB to master (guest is always active here) it keeps throwing the same error.

The strange thing is, that the login has been explicitly added as a user to the database and should therefore be allowed to connect, even if there are no further permissions (view definitions etc).
Jul 19, 2010 at 04:50 AM WilliamD

Hi Anuj,

I cannot see how this will help. We do not have local user accounts for SQL Server on any other machine, we use domain accounts that are assigned within SQL Server.

The user gets into the server, it is the authentication process which is causing the error.
Jul 19, 2010 at 06:02 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x164
x87

asked: Jul 19, 2010 at 03:45 AM

Seen: 1912 times

Last Updated: Jul 20, 2010 at 12:01 PM