April 19, 2010 at 10:06 am
Hi all,
I would like to create a SQL Server login from a domain group for dba purpose.
For this i create a new SQL Server login with this domain group. I grant sysadmin role to this group.
When i connected with my domain user (who is a member of this domain group) i can't connect to SQL Server. I receive the next error:
'Login failed for user 'DOMAIN\USER'.
I don't what i'am doing wrong.
Somebody can help me?
Thanks in advance.
April 19, 2010 at 12:29 pm
did you create SQL authenticated account or a windows account? It needs to be a windows account.
Post the login error message from the SQL error log with the state value.
---------------------------------------------------------------------
April 20, 2010 at 12:16 am
I create a windows account with a 'DOMAIN\GROUP'.
The error message is:
Login failed for user 'DOMAIN\USER'. (Microsoft SQL Server, Error: 18456).
I try to connect to SQL Server database with 'DOMAIN\USER' who is a 'DOMAIN\GROUP' membership.
April 20, 2010 at 2:21 am
hi
can you post a screenshot of the server logins from management studio
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 20, 2010 at 2:41 am
Hi Perri,
Server logins folder have the next information:
##MS_PolicyEventProcessingLogin##
##MS_PolicyTsqlExecutionLogin#
distributor_admin
DOMAIN\GROUP
DOMAIN\USER
NT AUTHORITY\SYSTEM
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
sa
SERVER\administrator
where SERVER is the servername who SQL server databas is running.
April 20, 2010 at 3:28 am
we need the STATE value from that error message.
Are you a member of this windows group?
---------------------------------------------------------------------
April 20, 2010 at 3:39 am
Yes, the user 'DOMAIN\USER' is member of 'DOMAIN\GROUP' group.
The messages that i can see in the SQL Server Log are:
First message:
Message
Error: 18456, Severity: 14, State: 11.
Second Message:
Message
Login failed for user 'DOMAIN\USER'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
April 20, 2010 at 3:56 am
State 11 means 'Valid login but server access failure'
check in AD that the group and user are allowed to access the server.
---------------------------------------------------------------------
April 20, 2010 at 8:16 am
The DOMAIN\GROUP is DOMAIN admin group.
The DOMAIN\USER is DOMAIN\GROUP member, and for this DOMAIN\USER is a domain administrator.
At the same time i add both DOMAIN\GROUP and DOMAIN\USER as member of SERVER\administrators group.
I already add SERVER\administrators (as BUILTIN\administrator) as windows login in SQL Server but i'm receiving the same error message when i try to connect with my DOMAIN\USER.
I don't understand this comportament. I don't know what i'm doing wrong. I only want to create a SQL Server login with a DOMAIN\GROUP.
In this form i havent to create a newly login in sql server database when a new DOMAIN\USER have to access to sql server database; it is sufficient with add this DOMAIN\USER as member of DOMAIN\GROUP in AD.
Somebody can help me? (
Many thanks in advance.
April 28, 2010 at 9:54 am
I have to say that SQL Server 2008 is installed on Windows 2008 Server.
I have test to create a group login in a new Windows 2003 environment (with SQL Server 2008) and i have create a group login (local group login and algo a domain group login) without problem.
Somebody have te same problem? It would be operative system problem? I have to do adittional action to create a wiindows group in SQL Server installed over Windows 2008 operative system?
Many thanks in advance.
April 28, 2010 at 10:22 am
ico-601891 (4/20/2010)
The DOMAIN\GROUP is DOMAIN admin group.The DOMAIN\USER is DOMAIN\GROUP member, and for this DOMAIN\USER is a domain administrator.
At the same time i add both DOMAIN\GROUP and DOMAIN\USER as member of SERVER\administrators group.
I already add SERVER\administrators (as BUILTIN\administrator) as windows login in SQL Server but i'm receiving the same error message when i try to connect with my DOMAIN\USER.
I don't understand this comportament. I don't know what i'm doing wrong. I only want to create a SQL Server login with a DOMAIN\GROUP.
In this form i havent to create a newly login in sql server database when a new DOMAIN\USER have to access to sql server database; it is sufficient with add this DOMAIN\USER as member of DOMAIN\GROUP in AD.
Somebody can help me? (
Many thanks in advance.
try adding the DOMAIN\GROUP directly into SQL Server as a login and assign permissions!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 28, 2010 at 10:34 am
Hi Perry,
I already have do this test.
This is my configuration:
- DOMAIN\GROUP --> I add this domain group as member of server administrators group.
- DOMAIN\GROUP --> I add as SQL Server login, with sysdba permission.
I try connect to SQL Server with my windows user (DOMAIN\USER) who is a DOMAIN\ADMIN GROUP member but the connection failed.
The only difference that i can see is the operative system (this configuration is running correctly in Windows 2003 but not in Windows 2008).
I dont understand this comportament. Somebody have the same problem?
Thanks newly in advance.
April 29, 2010 at 6:54 am
Can you post the error you're getting? Can you ping the SQL Server? Can you telnet to the port that it's listening on?
K. Brian Kelley
@kbriankelley
April 29, 2010 at 9:08 am
Hi K.Brian,
The error i'am getting is: "Login failed for user 'DOMAIN\USER'. (Microsoft SQL Server, Error: 18456)".
I can ping mi SQL Server and I can't telnet to the port that it's listening on (but i can connect via SSMS 2008 to my database).
However i have to say that all the tests i have comment i have made by connecting first to the server with the remote desktop, ie all test i have made "in the server" (not remotely).
Repeating the tests from a remote client (without remote desktop) i could get i wanted funtionality.
But why i can't get this funcionality when (after i'am connecting to server via remote desktop) i try to connect to SQL Server from the same server (ie, not remotely)? As i say in a previous post
it is possible in a windows 2003, but why in windows 2008 not? There are any windows security restriction?
Many thanks in advance.
May 10, 2010 at 3:02 am
Hi all,
I finally found the answer to my last question: once made the changes at the level of SQL Server i had to do a loggoff of my remote session and then newly login and then it works.
Thank you very much for all.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply