Category Archives: Admin

Admin, Scripts, Security

Easy Security Fixes

Leave a comment

Here are some security tasks I’ve been tackling lately.

1.  Find weak passwords and change them.

I keep track of application logins and passwords using a product called KeePass. It has a plugin called strengthreport that you can use to make a report or .csv showing the strength of your passwords. Use the SVN command to checkout that code to a local directory and then copy the StrengthReport folder to wherever you have KeePass installed. Restart KeePass and under the tools menu, you find a few new entries, all labeled ‘Create Report’. Pick one, they all seem to do the same thing. Then generate a report and send to .CSV.

You can filter on the ‘Basic Strength’ column or you can create a column with a single number in it to sort on.

Create a formula based on the ‘Basic Strength’ column: =FIND(“/”,D63)
Then in the next column over do something like this: =MID(D63,E63-1,1)
You could do that all in one block if you’re a fancy sort of excel wizard.

Now that the easy part is done, you can start working with developers to pump these passwords up.

2.  Set policy on logins so that they abide by Active Directory rules on complexity and failed attempts.

You can enable CHECK_POLICY on your logins by running some similar code to what is shown below.  The logins do not unlock themselves after the cooloff time defined in AD.  That’s not great.  Also, instead of clearing the ‘locked’ box on the login, just disable and re-enable CHECK_POLICY for the login (enforce password policy). This will prevent having to change the password, which will in-turn, stop developers from hating you.

 3.  Make sure logins are created properly in the future.

In KeePass, you can define an autotype template for each folder of logins.  This means you can do something like this and variables will be substituted into the template, providing you will a quick way to create users.

CREATE LOGIN [{UserName}] WITH PASSWORD=N'{Password}’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

Just create the login in KeePass, click into a SSMS window, switch back to KeePass and CTRL+V to make that code jump into SSMS.

You can also handle this is with a SQL Server Management Policy using the login facet and the PasswordPolicyEnforced property. That’s the way to go!

Active Directory, Admin

ADExplorer

Leave a comment

Need to find out who is in a global group?  Where I work, they don’t install management tools on our computers if you’re not in the security or server group.  Sysinternals to the rescue.

http://technet.microsoft.com/en-us/sysinternals/bb963907.aspx

We keep our users and group in a container named _users. I just navigate to that node and search the container for whatever group I’m looking for.  The group has a members property you can double-click to view.

Admin

Some Transaction Log Smarts

Leave a comment

Paul Randal writes about and demonstrates the workings of the transaction log.  

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Some reasons why your transaction log will not or is not shrinking.

http://msdn.microsoft.com/en-us/library/ms345414.aspx

A query to see why your log cannot be reused and is stopping the function of your database

SELECT name,log_reuse_wait_desc
FROM MASTER.sys.databases

The meanings of log_reuse_wait_desc are shown in the msdn article linked to above.

And finally, some steps for what to do when your log is full.

http://msdn.microsoft.com/en-us/library/ms175495%28SQL.90%29.aspx

Admin

Helping out the auditors

Leave a comment

If you’d like to give an auditor their very own copy of your data on their machine and your DB is less than 4 GB, then read on.  As a responsible IT person, you won’t be putting sensitive information on this auditor machine, unless it is properly secured, electronically and physically, right?

1. Set up the auditor machine

Install SQL Express on the auditor machine. Enable remote connections via TCP/IP with the config tools. Grant permissions for your agent account on the source server to be an admin on the auditor’s machine. You’ll also need to grant access for the agent account to write to a backup directory on the auditor machine.

2. Create a script that will restore the database and do some other steps. This script is referenced from sqlcmd in step 3. My script does these things:

MASTER.dbo.usp_killusers ‘MyDB’ –something similar to this script ->> http://www.sqlservercentral.com/articles/Administration/71/

RESTORE DATABASE MyDB FROM DISK = ‘\\workstation\backup\MyDB_Auditor.bak’
WITH REPLACE,
MOVE ‘MyDBtrainingsys’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf’,
MOVE ‘MyDBtrainingdata’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_1.ndf’,
MOVE ‘MyDBtraininglog’ TO ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB_2.ldf’

–Drop our auditing ddl trigger from the database
IF EXISTS (SELECT * FROM sys.triggers WHERE name = N’DBA_Audit’ AND parent_class=0)
DROP TRIGGER [DBA_Audit] ON DATABASE

EXEC sp_changedbowner ‘sa’

–Add rights for whomever needs access
CREATE USER [Domain\AuditorLogin] FOR LOGIN [Domain\AuditorLogin]
EXEC sp_addrolemember N’db_datareader’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_datawriter’, N’Domain\AuditorLogin’
EXEC sp_addrolemember N’db_ddladmin’, N’Domain\AuditorLogin’

3. Create the job with these steps on your source server.

(T-SQL type) – Create backup on workstation
BACKUP DATABASE MyDB TO DISK = ‘\\workstation\Backup\MyDB_Auditor.bak’

(OS Command) – Restore
sqlcmd -i \\Share\auditorRestore\MyDBAuditorRestore.sql -Sworkstation\sqlexpress -o \\share\sf.log

(OS Command) – Cleanup
del \\workstation\backup\MyDB_Auditor.bak

Admin

Rebuilding a Maintenance Plan

Leave a comment

Today I needed to rebuild a maintenance plan with specially selected databases. Anyone who has done it know that the interface for selecting databases lacks in one key area: the window cannot be resized and shows about 7 databases. My solution was to store the selected dbs for the plan in a temp table and then update the new plan with that info. Even the Green DBA knows a bit about the system tables, and isn’t afraid to play with them. That said, it’s a good idea to take a backup of the msdb database before doing anything to it.

SQL Server 2000 stores info related to dts and maintplans in the msdb. The two tables I’m concerned with are sysdbmaintplans and sysdbmaintplans_databases. There is an entry in the former for each maintenace plan, and an entry for each database used for the maintenance plan in the latter. They are associated by a guid. After knowing this, the rest is pretty easy. Definitely better than checking 80 boxes for me.

–insert database and plan id from plan we need to rebuild into a temp table
select my_db_name, plan_id into user_myDBsforOtherPlanfrom
(SELECT sysdbmaintplan_databases.database_name AS my_db_name, sysdbmaintplan_databases.plan_id as plan_id
FROM sysdbmaintplans
INNER JOIN sysdbmaintplan_databases ON sysdbmaintplans.plan_id = sysdbmaintplan_databases.plan_id
WHERE (sysdbmaintplans.plan_id = ‘{699A6A76-2BC3-4EA6-BAF1-0E268F82B095}’)) myDBsforOther

— go create your new maintplan and just select 1 db
–find the new guid and substitute in the queries below
select * from dbo.sysdbmaintplan_databases

–we need to find the plan id for the new plan and update our temp table, so they match. This could just go in the third step as a constant i suppose.

update user_myDBsforOtherPlanset plan_id = ‘{B9051EA2-F303-4E4F-BB17-FBF26E9B0C48}’

–insert the rows into the sysdbmainplan_databases table – except the one we added to the newly created plan

insert into dbo.sysdbmaintplan_databases (plan_id, database_name) select my.plan_id, my.my_db_name from dbo.user_myDBsforOtherPlan as my , dbo.sysdbmaintplan_databases notmine
where my.plan_id = notmine.plan_idand my.my_db_name <> notmine.database_name