Author |
Topic |
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-04-24 : 07:53:54
|
Hi all,I'm starting a new contract later on this week, and I have absolutely no idea what state the client's SQL databases are in. From what I understand I'm the first DBA they've ever had, I presume up to now they've simply installed their RDBMS and DB's in "click this button" style according to the instructions of their 3rd party vendors and the like. So, although not strictly a 'greenfield' site, it is indeed a green one (literally as well as figuratively, but I digress!!)Any of you guys know of a good script that I can run to give me a snapshot of the health of their databases/server config settings, listing frequency/type of backups, performance stats, trans log utilisation, security etc????Of course I could perform this audit manually, one DB and one server at a time, and will likely perform an exhaustive one anyway this week, but I'd rather know frome Day ONE whether there are any glaring issues that urgently need to be addressed.Cheers, |
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-04-24 : 08:29:15
|
Work backwords from the one with the most danger to you staying in this job.a) backupsb) securityc) performance hardware db implementationd) resiliance (fault-tolerance)As you knock off each successive objective, the paycheck will settle longer and the ability to react to the next objective becomes easier. |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-04-24 : 08:59:41
|
quote: c) performancehardwaredb implementation
http://www.sql-server-performance.com/sql_server_performance_audit.asp |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-24 : 09:19:54
|
Besides other items mentioned, I would run these at the first opportunity on each database:DBCC CHECKCATALOG ( 'database_name' ) DBCC CHECKDB ( 'database_name' ) If there is any data corruption you will want to find it right away, if for no other reason than making sure you don't get blamed for it.CODO ERGO SUM |
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-04-24 : 09:56:25
|
quote: Originally posted by AndrewMurphy Work backwords from the one with the most danger to you staying in this job.a) backupsb) securityc) performance hardware db implementationd) resiliance (fault-tolerance)As you knock off each successive objective, the paycheck will settle longer and the ability to react to the next objective becomes easier.
That was pretty much my intended order of priorities, the Backup/Performance/hardware I'm fairly familiar with, but is there a refresher article on SQL Security Auditing? As well as tempdb/trans log sizing?? It's been a few years since I configured security at my last job and really haven't kept fresh on it!Cheers,Jaybee. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-04-24 : 12:12:53
|
from some vague memory of my role as DBAsecurity...account that runs the serviceschange the password after you've checked dependenciesbefore changing SA password, verify if there are tasks dependent on it like replication or linked serverscheck for builtin administrator, users with sa or dbo rightschecked if there are audit tables/database, triggers, linked servers, dts packages, jobs, seal them off one by one--------------------keeping it simple... |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-24 : 15:27:09
|
There is the SQL Best Practices thingie-tool that MS offer for download. That might give you a nice 900 page report that you can pretend that you wrote and send in a large bill for ... email me for my Swiss bank account for my 10% please Kristen |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-24 : 16:12:20
|
quote: Originally posted by Kristen...a nice 900 page report that you can pretend that you wrote and send in a large bill for...
Do you work for Accenture?CODO ERGO SUM |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-24 : 17:08:45
|
No, but one of my old office juniors works for Avanade - he's done OK with my basic training course - you know, two Ferrari's, big country house, that sort of thing ... Kristen |
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-04-27 : 06:22:06
|
[quote]Originally posted by Kristen No, but one of my old office juniors works for Avanade - he's done OK with my basic training course - you know, two Ferrari's, big country house, that sort of thing ... Hi, a few questions on Security - my weakest area of SQL - if I may;Builtin\AdminI've noticed that they are still present on the servers, are they still a threat in 2k as they were in 7.0, and more crucially, is there a way to check if anything depends on them? Secondly, I remember from the dim and distant past, every time I dropped the Builtin account, I was no longer able to schedule a bean. Is this still the case?SA passwordI'm logging on to with EM using only my Windows login (which is also a member of the NT Admin group). The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?NT AdminsI want to be able to seal off the NT Admins group and have myself as King of the SQL servers - what would be best practice here?UsersIs there a programmatic way to check which users have Admin rights to the database?ProcessesIs there a way (programmatic or otherwise) to check which process depend on what logins?Cheers,Jaybee |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-27 : 06:53:02
|
"The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?"Just try to connect with a blank password.CODO ERGO SUM |
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-04-27 : 07:39:18
|
quote: Originally posted by Michael Valentine Jones "The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?"Just try to connect with a blank password.CODO ERGO SUM
Well, yeah, but it's not prompting me for my password, I just get validated!Jaybee. |
|
|
pootle_flump
1064 Posts |
Posted - 2006-04-27 : 08:46:55
|
I think Michael means that if you can connect to the db as SA with a blank password then the password is blank. If you get a "incorrect password" message then it isn't. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-04-27 : 16:47:23
|
There's a security checklist on www.sqlsecurity.com. They also have free analysis and tools sections. I really like the checklist personally.I would also use the best practices tool from MS. I run that for all my clients, look at the backups, run through the security checklist, perform basic perfmon and profiler checks, look for maintenance routines, and create an executive summary report of all the findings with basic recommendations. It's a good way to start a relationship. Sometimes, they don't like the recommendations, but that's life.quote: Builtin\AdminI've noticed that they are still present on the servers, are they still a threat in 2k as they were in 7.0, and more crucially, is there a way to check if anything depends on them? Secondly, I remember from the dim and distant past, every time I dropped the Builtin account, I was no longer able to schedule a bean. Is this still the case?
You should setup an AD group with your approved SQL Server administrators. Add that to the instances with sa privilege and drop the BUILTIN\administrator account. Do it in that order, or you'll be in trouble. You can schedule things just fine still. quote: SA passwordI'm logging on to with EM using only my Windows login (which is also a member of the NT Admin group). The password box in the SA account has a string of asterixes, but that doesn't indicate whether it's encrypted or simply blank - is there a way to check?
Login to the instance with Query Analyzer. Change it to use SQL Server authentication and the sa username. Leave the password blank. If you can login, they are idiots. quote: NT AdminsI want to be able to seal off the NT Admins group and have myself as King of the SQL servers - what would be best practice here?
Follow the advice in the BUILTIN\Administrator section. Additionally, there's no way to practically isolate themselves from the physical server. Auditing is a good thing though.quote: UsersIs there a programmatic way to check which users have Admin rights to the database?
Follow the advice above. Write scripts to check for groups or users who are part of the server level roles, have dbo rights in the databases, or have elevated object level permissions. You can find out all the information on the system tables in Books Online. I think Nigel Rivett or Vyas might have some scripts for this online. I would check there and the script library on sqlcentral.com.quote: ProcessesIs there a way (programmatic or otherwise) to check which process depend on what logins?
Can you explain what you mean by processes?? If you mean Windows services, it's easy. If you mean processes like scheduled jobs, you can check through system tables. Describe a little more about what you want.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|