SQL Agent Properties

It’s been a rather windy summer thus far, making it less fun to be out on the water.  I don’t know many guys who enjoyed being blasted by the wind while bobbing up and down on the waves for hours on end.  I went out on Pewaukee Lake a few weeks ago with a buddy from work.  We had picked the day in advance since it was supposed to be dry and calm.  We got the dry, but not the calm.  We had a stiff wind blowing out of the west that drove us back into the western half of the lake after trying to fish the narrows in the middle.

I spent the day focusing my fishing efforts on making the bait look real.  I tried hard to avoid retrieving the lure in a rhythmic fashion.  I was paid off with a nice upper 30s muskie:

June 2017 Pewaukee Muskie.jpg

My fishing buddy hooked one a short time later, but couldn’t keep it pinned and we lost it.

Recently, I blogged about migrating the SQL Server installation onto a different drive.  I did find one problem after this move that I had to address.  I ran into a problem with the SQL Agent and I wasn’t able to diagnose the issue.  If I remember correctly it was actually an SSRS subscription that failed, and I needed details to find out why.  I found that the SQL Agent has properties, and the error log was still pointing back at the previous location on the C: drive, which no longer existed.  There is a stored procedure you can execute to see those properties, in addition to looking at them in the SSMS UI:

EXEC msdb..sp_get_sqlagent_properties

20170712 SQL Agent Properties.PNG

Lastly, just update the value with the corresponding SET stored procedure and restart the SQL Agent:

EXEC msdb..sp_set_sqlagent_properties 
   @errorlog_file = N'D:\SQLSERVERINSTALL\SQLAGENTERRORLOG.log'

Now your SQL Agent properties have been update.

Advertisements

Finding SSIS package passwords in SQL Agent jobs

It can be a daunting experience to try fishing a new lake. We can see what the lake looks like from above the water, but below there are innumerable structures and features to consider. Last summer I fished lake Winnebago for the first time with my pop and brother. Lake Winnebago is the 21st largest natural lake in the United States, covering about 215 square miles. As we motored out from the boat launch, the only thought was “where to start?” The vast lake was stretched out before us, but since we could only see the top of the water we had to use other clues to figure out where to start fishing.
It’s tough heading into the unknown. Earlier this year I started a new job that used SQL Server Integration Services (SSIS) jobs saved in the file system as steps in various SQL Agent jobs. This job was at a small shop where documentation was not well kept. The SSIS packages were password protected, but nobody knew the password! Fortunately I was able to find the passwords stored in the SQL Agent job steps using this SQL command:

SELECT  sj.name ,
        sjs.command
FROM    msdb.dbo.sysjobs sj
        JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE   sjs.subsystem = N'SSIS'

After running, I was able to find the /DECRYPT password part of the command, which I used to decrypt and view/edit the packages using BIDS.  In the image below, you can see the password is “ssis”.

20141229 SSIS Package Passwords

Winnebago turned out to be a fun lake to fish.  We were catching fish all day, and ended up with a good amount to take home, after throwing back all the nasty sheepshead.

Sheepshead