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

PBM on Sql Server 2005 ? Expand / Collapse
Author
Message
Posted Sunday, April 14, 2013 2:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
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..!!
Post #1442091
Posted Sunday, April 14, 2013 4:26 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
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 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 #1442097
Posted Sunday, April 14, 2013 9:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
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..!!
Post #1442157
Posted Monday, April 15, 2013 3:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
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?
Post #1442207
Posted Monday, April 15, 2013 3:18 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 @ 1:14 PM
Points: 40,615, Visits: 37,081
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 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 #1442211
Posted Monday, April 15, 2013 3:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
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..!!

Post #1442221
Posted Tuesday, April 23, 2013 7:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1445421
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse