PBM on Sql Server 2005 ?

Question

PBM on Sql Server 2005 ?

rollercoaster43

Ten Centuries

Points: 1306
More actions
April 14, 2013 at 2:03 am

#273593

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..!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 Ten Centuries Points: 1306 More actions · Answer 2

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 Ten Centuries Points: 1306 More actions · Answer 3

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_descpermission_nameendpoint_namestate_descgrantorgrantee

SERVERVIEW ANY DATABASENULLGRANTsapublic

ENDPOINTCONNECTTSQL Local MachineGRANTsapublic

ENDPOINTCONNECTTSQL Named PipesGRANTsapublic

ENDPOINTCONNECTTSQL Default TCPGRANTsapublic

ENDPOINTCONNECTTSQL Default VIAGRANTsapublic

Please suggest if the approach I had taken was incorrect?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 Ten Centuries Points: 1306 More actions · Answer 5

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 SSC Guru Points: 182276 More actions · Answer 6

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