Nice thought

Not every post here needs to focus on some technical bug or (hopefully) solution.  Here is a thought I came across today that really struck me.  Enjoy.

“All programmers are optimists. Perhaps this modern sorcery especially attracts those who believe in happy endings and fairy godmothers. Perhaps the hundreds of nitty frustrations drive way all but those who habitually focus on the end code. “ 

- Frederick Brooks

Posted in Uncategorized | Leave a comment

Defend your SQL Server Instance

This is a quick post, but hopefully it will save some of you some headaches down the road.   We recently purchased vulnerability management software to test and harden our internal and external environment.  I manage an Operational Data Store running on SQL Server; that instance uses the “sa” login (as many do).  The new software package attempts to log into any databases it thinks it found (by sensing the availability of the management port on a server) and the first user it tries?  SA.

One of the “features” introduced in SQL Server 2005 and later is to have the SA user lock-out if there are too many failed login attempts.  This happened to our DB over the weekend, affecting some of our jobs and processes adversely.  While the software was not able to log in (the password is sufficiently complex to prevent that) it did highlight a basic hole in the system that was so easy to fix.

Rather than leave a user with the kind of power of “SA” hanging out there, you can simply re-name the user and increase security by a large factor.  By re-naming the SA user you are now forcing someone to guess both a user name AND a password to gain entrance.  You effectively double the security around the SA user/role.

Here is the SQL to re-name the SA user.  This will re-name the SA user to the user name of your choice immediately:

ALTER LOGIN sa WITH NAME = [your new user name]
GO

While this is the best route, if you are not in a position to change the SA user immediately, make sure you map at least one user in your enterprise into the instance using AD Authentication with the “sysadmin” role. Membership in this role will ensure that user can unlock the SA user (or any other user) gets locked out.

Good Luck!

Posted in Security, SQL Server | Leave a comment

Copy and Paste on Metro Win8 Remote Desktop

So I am using the new Metro UI version of Remote Desktop. I like the App and think it is a step forward for those of us on multiple machines via RDP all day long. One thing that was bugging the crap out of me was that copy and paste seemed to be broken in the Metro version of the app. If I started Remote Desktop form the actual Desktop, copy & paste works fine.

Seems something gets out-of-whack with a little process called rdpclip.exe on the server-side of the relationship. I think this will be a persistent issue if you bounce between the Metro and legacy RDP clients. Here is a work-around:

1) On the server, start task manager and locate “rdpclip.exe”.
2) Kill the instance that is running under your username.
3) Manually start a new instance of rdpclip.exe from the command line

That should do the trick. If that fails to get copy and paste working for you,. be sure you have Clipboiard set to “On” for the Remote Desktop App (In the app’s settings).

Good Luck!

Posted in Uncategorized | Tagged | Leave a comment

Importing Excel Data with 64-Bit SSIS

I wanted to run a simple import of an Excel worksheet to a SQL Server table this morning, only to find the data flow errored out because I happened to be running a 64-Bit install of Visual Studio (SSIS).

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Turns out there is a simple work-around should you want to just one a 1-time import like I did.  In the project properties, under “Debugging” you can flag if the process should run as 64-Bit or not (See Below).  If you need to do this on a regular basis, just make sure you execute your package using the binaries in “Program Files (x86)” and you should be just fine.  For my needs the 1-time tweak sufficed.

Posted in ETL, SQL Server, SSIS | Leave a comment

SQL Server: Count Available Working Days

I wanted a quick and easy way to determine working days for a given date range.  I use this most often in BI/reporting applications specifically when dealing with trending and forecasting but I would assume it has a host of various applications.  In the hope that is the case, I thought I would throw it out here for the benefit of all.

Prerequisites

I want to account for both weekends AND scheduled days off (holidays).  To accomplish that, you will need a simple “holidays” table in the DB to do the final filter against.  Here is the structure I used:

CREATE TABLE [dbo].[ctrl_Holidays](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[DATE] [smalldatetime] NOT NULL,
CONSTRAINT [PK_ctrl_Holidays] PRIMARY KEY CLUSTERED

THE CODE

To get the working days, I built a stored proc in SQL Server that accepted a start and an end date.  My calculator is inclusive, meaning the start and end days are counted.  The process is straightforward enough that you could easily exclude one or both of those if needed.

The SP accepts two parameters: @startDate & @endDate, both of type smalldatetime.  Here is the high-level process flow:

  1. Build a temp table to hold each date in the range and a bit indicating if it is a work day.
  2. Determine if start day is a weekend day (using DATEFIRST = 1, Monday) and if it is not, add it to the table
  3. Next, loop while the date is less than the end date specified inserting all dates into the table (Nothing to do with the work day bit is processed, it is NULL at this point)
  4. After looping to create each date, then update the temp table to flag non-work days (in this case DATEPART dw equals 6 or 7 (Saturday or Sunday if DATEFIRST equals 1).
  5. Then take a COUNT of work days from the temp table and subtract it from the count of days falling in the requested range from the Holiday table.  There you have it.

From there you can see the meat of the SP below.

DECLARE @WorkDays INT
DECLARE @BusinessDays INT
DECLARE @dayTracker TABLE (calendarDate datetime, isWorkDay bit);

SET DATEFIRST 1
-- insert our starting date
IF DATEPART(DW, @startDate) = 6 OR DATEPART(DW, @startDate) = 7
INSERT INTO @dayTracker
VALUES (@startDate, 0);
ELSE
INSERT INTO @dayTracker
VALUES (@startDate, 1);

-- add dates into table from start to end date
WHILE (SELECT MAX(calendarDate) FROM @dayTracker) < @endDate
INSERT INTO @dayTracker
SELECT DateAdd(dd, 1, MAX(calendarDate)), NULL FROM @dayTracker

-- update table to tag work days
UPDATE @dayTracker
SET isWorkDay = CASE
WHEN DatePart(dw, calendarDate) IN (6, 7)
THEN 0
ELSE 1
END
WHERE isWorkDay IS NULL

SELECT @WorkDays = COUNT(*)
FROM @dayTracker
WHERE isWorkDay = 1;

SELECT @BusinessDays = @WorkDays - COUNT(*)
FROM ctrl_Holidays h
WHERE h.[DATE]
BETWEEN @startDate AND @endDate;

RETURN @BusinessDays;
Posted in SQL Server | Leave a comment