If you work on a computer, get these tools

So much of IT comes down to tools. I love getting a recommendation from a colleague that improves my workflows. Here are a few of my favorites that can benefit anyone, no matter what your specialty. I do database work, so my examples come from that area. I use these on Windows, but some tools are available for other platforms.

Keepass: Password storage. Put this auto-type behavior on the folder that holds all your sql logins and press control-v after you’ve just come from a sql query window. So great!

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

For my personal info I use 1password. People without password managers drive me crazy. Watching them work the internet makes my head hurt.

Sublime Text: This text editor can do multiline editing better than SSMS does it. Say you have a list of tables you need to drop. Copy that list into Sublime Text, do a little key command to get your cursor on every line and then start typing the drop statement. Sure you could do this with some powershell, but some reason, this is just really fun. This one costs money.

Ditto Clipboard Manager: Back at the beginning of my career, I had a co-worker who wouldn’t stop raving about his clipboard manager. I ignored him and I was WRONG to do that. Eliminates so much extra tabbing and thinking. If you copied it – it’s in a little pop-up menu you can activate with a key command. I use ctrl-~.

Path Copy Copy allows you to copy UNC paths from your mapped drives. Instead of getting L:\folder, you’ll get the shareable UNC path like \\server\sharename\folder.

Tweaked script to find all db_owner members

Doing some security cleanup. This script is useful. Ran it against all servers in the environment and needed to fix it up a little to work. Finds members of db_owner,db_accessadmin. However, I don’t think it elaborates on what login dbo is mapped to. I’ve included a script below that checks for that – also tweaked to fix.

DECLARE @databaseName SYSNAME ,
@sql VARCHAR(1000)
DECLARE @Tbl TABLE
(
DatabaseName VARCHAR(200) ,
RoleDesc VARCHAR(100) ,
UserName VARCHAR(100)
)
DECLARE databaseCursor CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE state = 0
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @databaseName
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'SELECT ''' + @databaseName + ''' AS [Database],
USER_NAME(role_principal_id) AS [Role],
USER_NAME(member_principal_id) AS [User]
FROM [' + @databaseName
+ '].sys.database_role_members
WHERE USER_NAME(role_principal_id) IN (''db_owner'', ''db_accessadmin'') and USER_NAME(member_principal_id) <> ''dbo'' '
INSERT INTO @Tbl
EXEC ( @sql
)
FETCH NEXT FROM databaseCursor INTO @databaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
SELECT *
FROM @Tbl

dbo login mapping:

--Script : Verify the database owner and DBO User owner are the same
--When a login is defined as the DBO user, he/she has FULL control within the database
set nocount on
go
Create table #TmpTableSec1 (database_name varchar(100), Owner varchar(100))
Create table #TmpTableSec2 (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
Create table #TmpResult (database_name varchar(100), principal varchar(50), DBO_Owner_Login varchar(100))
DECLARE DBCURSOR CURSOR FOR
select name
from sys.databases where state=0 and name not in ('tempdb')
Declare @name varchar(100)
Declare @cmd varchar(200)
Declare @dbowner varchar(100)
Declare @DBO varchar (100)
OPEN DBCURSOR
FETCH NEXT FROM DBCURSOR INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'Database --> ['+@name+']'
set @cmd = 'select name, suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
--select @cmd
insert #TmpTableSec1 exec (@cmd)
--select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
set @cmd = 'use ['+ @name +']
select db_name(), name, suser_sname(sid)
from sys.database_principals where name = ''dbo'''
--select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
INSERT #TmpTableSec2 exec (@cmd)
--print ''
FETCH NEXT FROM DBCURSOR INTO @name
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
go
insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name
print '--- Database Owner & DBO User Mapping ---'
print '*****************************************'
select * from #TmpResult
print '--- Orphaned DBO Logins ---'
print '***************************'
select * from #TmpResult
where DBO_Owner_Login not in (select name from sys.server_principals) or DBO_Owner_Login is null
drop table #TmpTableSec1
drop table #TmpTableSec2
drop table #TmpResult
set nocount off

Thanks to the sqldude and msft forum members for providing these nearly complete scripts:

Verifying DBO User ownership and track users without a SQL login but via AD Group Logins

Running a plethora of T-SQL

Have you ever needed to run a bunch of scripts, over and over and over? And the SSMS tabs – it can be a mess. This is mind-numbing work – call in the computers. Check out this script I forked from GitHub and modified: https://github.com/propellor/Powershell-pasen/blob/master/Run-Sql.ps1

You point the script it at a folder of scripts and it will fire them off using invoke-sqlcmd. You can use it in a sort-of debugging mode, to get those scripts all working when you get a mess from a co-worker or vendor and have it pick up rerunning from the failed script. Alternatively, if someone else is responsible for the fixin’ and you are just the runner, you can have all the failed scripts rename themselves to .failure.

Also, my first GitHub fork.

UPDATE: Since doing this, I’ve started using RoundhousE, which does the same thing, but waaaay better. Using TFS and octopus deploy, we package up roundhouse with some sql scripts and let developers deploy database scripts to staging and then production.

Easy Security Fixes

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!

Easier SSRS log querying

I’m working on setting up a scale-out SSRS installation.  We’ll be shutting down the old URL soon and everyone should be off it.  I can tell who is still hitting the old URL, which leads to the old server by using this query.

We’re using the ExecutionLog2 view in the reportserver database. Much easier than joining tables together and trying to build paths to reports with a CTE.

More information on this view.
http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx

SQL Server Reporting Services scale-out cluster problems

I’ve been trying to get an SSRS scale-out cluster working the past few months.  I’d given up today and resorted to calling MSFT support.  I’d read all the manuals and supplemental articles like a proper admin should.  I’d posted questions on the MSFT forums to no avail.  Time to bite the bullet and sit on the phone with an expert.

I was getting 401 errors with not much help from the error logs.  A 401 is a security error.  The problem was intermittent, sometimes happening, sometimes not.  The problem always went away when a node was removed from the load balancer rotation.  Major hint there.  When you connect to the load balancer, you are routed to the same SSRS node – until you time out at 10 minutes – this is called “Sticky Sessions”.  The SSRS servers also got this treatment from the load balancer – and that was the problem.

The tech and I went through hours of checking configurations until he asked me to add an entry to the hosts file for the F5 name which reporting services was using.  This would make node 1 always request data from SSRS on node 1 and node 2 always request data from node 2.  Fixed the issue.  I imagine we can configure this on the load balancer as well, which I will probably have done.

DBase

Here at work, we need to import from DBase files every now and again.  DBase is a strange breed of database if you’ve been working in SQL.  To import from SQL, you use a OLE DB connection and then pick the folder that contains your DBase file – which is actually just one table.  http://msdn.microsoft.com/en-us/library/aa337084.aspx

I seem to remember running into a problem with the length of the DBase file name a year or so ago, so if you’re still having problems try shortening the name.

Inserting results of FILELISTONLY to a table

I was looking for a code example of this on the web, but couldn’t find it, so here it is. You would need to create the @cmd of course with your own variables.

SELECT  @cmd = 'restore filelistonly  from disk = ''' + @localBackupPath
+ @dbname + '\' + @filename + ''''
PRINT @cmd
CREATE TABLE #files
(
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] CHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] NUMERIC(20,0) ,
[MaxSize] numeric(20,0) ,
[FileId] BIGINT ,
[CreateLSN] NUMERIC(25, 0) ,
[DropLSN] NUMERIC(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] NUMERIC(25, 0) ,
[ReadWriteLSN] NUMERIC(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] NUMERIC(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(32)
)
INSERT #files
EXEC ( @cmd
)