Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PBM on Sql Server 2005 ?


PBM on Sql Server 2005 ?

Author
Message
rollercoaster43
rollercoaster43
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 434
Hi All,

I have a few critical sql 2005 production servers and I have been asked to try implement policies on them.

I have successfully evaluated policies on my 2008 instances, but is there a way to evaluate those policies against 2005 Databases?

I do not have any 2008 Instance from where I can register these servers and try to evaluate policies..

Any alternate solution would be Higly Appreciated!!!

Thanks..!!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
The only way is to evaluate the policies from a 2008 box. That or implement DDL triggers manually to match the policies you want, depending on the policy, that may be an option.


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


rollercoaster43
rollercoaster43
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 434
Hi

Thanks for the reply. So you mean to say I can register these 2005 sql servers on a sql 2008 instance and evaluate the Policies from there? Please let me know if my understanding is correct...

Thanks..!!
rollercoaster43
rollercoaster43
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 434
Also, I had a very scary moment todaye after trying to fix the non compliance for the policy 'Public not granted server role.'

I executed the below query to get rid of the policy violation :

REVOKE VIEW ANY DATABASE FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Local Machine] FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Named Pipes] FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Default VIA] FROM public;

Afte this, All the logins on my test server lost all their access and I could see the below error message in the errol logs :

Login failed for user 'username'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: Client IP]


When I execute the query to check the Public role, I get the below result which clearly shows that Public has the 4 default permissions which we can get rid of as per Microsoft Best Practice :

class_desc permission_name endpoint_name state_desc grantor grantee
SERVER VIEW ANY DATABASE NULL GRANT sa public
ENDPOINT CONNECT TSQL Local Machine GRANT sa public
ENDPOINT CONNECT TSQL Named Pipes GRANT sa public
ENDPOINT CONNECT TSQL Default TCP GRANT sa public
ENDPOINT CONNECT TSQL Default VIA GRANT sa public


Please suggest if the approach I had taken was incorrect?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
rollercoaster43 (4/14/2013)
So you mean to say I can register these 2005 sql servers on a sql 2008 instance and evaluate the Policies from there?


Should work, of course only policies that apply to SQL 2005.


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


rollercoaster43
rollercoaster43
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 434
Thanks Gail.

And for the above issue, I think I got the solution...
I had to explicityly grant connect on TCP Endpoint to every login after the Connect permission was revoked from Public on the TCP endpoint..


GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [loginname]

Thanks Again..!!
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8291 Visits: 14368
Technically you do not need a SQL instance at all to evaluate policies. I have my policies stored as XML files on disk and evaluate them against the instances in my environment (some 2005, some 2008 R2) using PowerShell. You can also evaluate them against 2000 but it so happens there are none of those left in the current environment.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search