May 5, 2011 at 9:46 am
I have set up a SQL2008 R2 server with named pipes disabled and the SQL Port set to 2433, in windows only mode. I read somewhere that as a security enhancement I could 'deny' the CONNECT ENDPOINT to public, then 'grant' the CONNECT ENDPOINT to valid logins. However as soon I run the deny on the 4 x CONNECT ENDPOINTS FROM Public and grant the 4 x CONNECT ENDPOINTS to the individual windows account, that windows account then can't logon to SQL? Via SSMS it will just say Login failed for user xxx. If I grant CONNECT ENDPOINTS back to Public, all is well again??
When I exec the following script, the connects and grants are all as I'd expect them to be for this to work:-
SELECT[Account] = prin.name,
CASE prin.is_disabled
WHEN '0' THEN 'Enabled'
WHEN '1' THEN 'Disabled'
END AS [Account Enabled],
[Class Desc] = perm.class_desc,
[Endpoint name] = e.name,
[Permission] = perm.permission_name,
[State Desc] = perm.state_desc
FROM master.sys.server_permissions perm
INNER JOIN master.sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id
order by prin.name, perm.permission_name
Can someone please let me know if this is a valid thing to do and what other privileges i need to give the individual window account that it doesnt have that Public does. The individual windows account does have read on Master. Additionally i've denied a number of Views, SP's etc to Public, so everything Public has 'execute' permission on, so does the individual window account.
Thank-you
May 5, 2011 at 10:18 am
I can't find any official articles that suggest denying access to the public group. The BOL article says to DENY CONNECT ENDPOINT to the EVERYONE group, which is different. I believe that DENY permission on public literally means no external access.
May 5, 2011 at 11:49 am
^ What Howard said. Everyone's in public at all times. All deny's on SQL Server are exclusive Denies, they can't get overridden. Deny connect to public locks up that endpoint entirely.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 10, 2011 at 4:23 am
Thanks guys. I get what you mean about Deny precedence, but please see this MSDN article...
http://msdn.microsoft.com/en-us/library/cc645930(SQL.100).aspx
Thanks
May 10, 2011 at 5:07 am
simon.letts (5/10/2011)
Thanks guys. I get what you mean about Deny precedence, but please see this MSDN article...http://msdn.microsoft.com/en-us/library/cc645930(SQL.100).aspx
Thanks
Ok....So where does that say anything about DENY? Revoke's entirely different
May 10, 2011 at 7:10 am
Thank-you. Have tested and can use revoke no problem, which will be fine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply