SQL Server Agent Jobs managed on the web and least privilege

I ran into an interesting scenario the other day at work with a SQL Server 2000 system. A developer was updating a web page that allowed business users to monitor/execute a SQL Agent Job or more to the point monitor and execute multiple jobs. This web page had been working fine for some time and they stopped working. As it turns out the permissions were changed on the account after the 3rd Party application was upgraded and the old permissions were reset. The root cause of the problem was a permissions change and the old permissions were set too high and gave too much privilege to the account. As a DBA, this frustrates me but we'll avoid that and get to the point of how to resolve the issue.

Here is the scenario...multiple SQL Agent Jobs being managed via an Intranet Web Page by the business end-users. The web page is connecting as a privileged user with sysadmin rights, this is never recommended. So how do we fix this?

The SQL Agent Jobs are using a mix of T-SQL and Operating System (CmdExec), the CmdExec requires SysAdmin rights to run. There is another option and that is to use a SQL Agent Proxy Account for the job, this account will not have SysAdmin rights and that means we can take away SysAdmin right from the other account. Now this creates another problem, now the web page throws an error saying it can't find the Jobs. The SQL Login is not a SysAdmin now and can't see the jobs, the problem is with the Owner of the jobs. The login must be the owner of the job in order to see or start/stop the job. The web page is also executing msdb.dbo.sp_help_job and the login needs to be granted the SELECT permissions on the msdb.dbo.sysjobhistory system table in order to view job history. All of this grants the least privilege to the login and helps to protect the server.

To summarize the above, here are the actions taken:
1) Create a SQL Agent Proxy Account
2) Run the job step(s) requiring higher privilege under the SQL Agent Proxy Account
3) Remove SysAdmin rights from the SQL Login
4) Grant least privilege to the SQL Login, the lowest rights required to access each database
5) Grant SELECT on msdb.dbo.sysjobhistory (System Table) to view Job History using msdb.dbo.sp_help_job (Stored Procedure)
6) Assign SQL Login as Owner of the SQL Agent Jobs being monitored and executed

For more information, check MSDN.

Permissions for sp_start_job:
http://msdn.microsoft.com/en-us/library/aa260289(SQL.80).aspx
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.


Permissions for xp_sqlagent_proxy_account:
http://msdn.microsoft.com/en-us/library/aa260700(SQL.80).aspx
Sets or retrieves the proxy account information used by SQL Server Agent and the xp_cmdshell extended stored procedure when executing jobs or commands for users who are not members of the sysadmin fixed server role. The proxy account is a Microsoft® Windows® account in whose security context the jobs or command prompt commands are run.

Intel Processor Identification Utility

I found a great utility from Intel that will tell you everything you wanted to know about that little silicon chip in your system that makes it run. Intel has two Processor Identification Utilities, one is for the new processor architecture and the other is for the old processor architecture. The general rule is Pentium IV and later processors use the Processor Identification Utility and processors older than Pentium IV will use the Processor Frequency ID Utility. To find out what utility to use, go to this page on Intel's website:
http://www.intel.com/support/processors/tools/piu/sb/CS-015472.htm

Intel Processor Identification Utility:
http://www.intel.com/support/processors/tools/piu/

Intel Processor Frequency ID Utility:
http://www.intel.com/support/processors/tools/frequencyid/

Large Memory Support in Windows Server 2003 x86

Have you ever built a x86(32-bit) Server with lets say 2GB of RAM and then later added more RAM bringing the total RAM to 3GB or 4GB? And then sat there scratching your head trying to figure out why an application such as SQL Server cannot utilize the memory beyond 2GB? I had this very issue happen the other week with Windows Server 2003 R2 SP2 (x86) and SQL Server 2005 Enterprise (x86) installed on a test server with 3GB RAM. I could not get SQL Server to allocate more than 1.7GB RAM and then remember the /PAE and /3GB switches in the Boot.ini file. I had completely forgotten about the settings since I've been working on mostly x64 systems in recent years. Once I added the switches and rebooted the system, SQL Server was able to allocate more memory and the problem was resolved.

Here is what's going on, Windows is not recognizing all of the memory in the system and it is unable to address it. Your next question should be does the hardware architecture support x64(64-bit), this may also be referred to as EMT64. 64-bit(x64) does not have the same limitations on memory as 32-bit(x86) and can address much more memory. Your system or software might not support x64, if that's the case then you're stuck using x86. If the system has an Intel processor, download the Processor Identification Utility from Intel to find the details of your processor and what it supports:
http://www.intel.com/support/processors/tools/piu/
I also have a blog post with information on the Intel Processor Utilities:
Intel Processor Identification Utility

Here are the steps to fix the memory issue so Windows Server 2003 can address the memory. Physical Address Extensions need to be enabled for the system, this is done by modifying the Boot.ini file. There are two switches that need to be present, maybe one depending on your system configuration. The switches are /PAE and /3GB. If the system has more than 16GB of RAM then remove the /3GB switches or the system won't see anything beyond 16GB. Also, these switches only apply to x86(32-bit) server operating systems. The Boot.ini can be edited by going to Control Panel > System > Advanced tab and clicking on the Settings button under Startup and Recovery then click Edit to change the Boot.ini settings. Be careful and double-check what you modify or the system might not boot. If the system doesn't boot after the switches are added, then boot to Safe Mode and change the settings back.

The following is an example of a Boot.ini file where the PAE and 3GB switches ha
ve been added:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /3GB

For more information, read the Microsoft KB article on the subject:
http://support.microsoft.com/kb/283037


If you're using Windows Server 2000 and SQL Server 2000, please refer to these Microsoft KB article for assistance.
How to configure SQL Server to use more than 2 GB of physical memory:
http://support.microsoft.com/kb/274750

SQL Server only uses 2 GB of memory even though the AWE option is enabled:
http://support.microsoft.com/kb/811891/

Carolina Mega Code Camp Announced...

Save the Date! The Carolina Mega Code Camp has been announced to be held on Saturday, October 10, 2009 at the Levine Campus of CPCC in Matthews, NC. This event is also known as Charlotte Code Camp.

"Developing for Dollars, our year-long focus on providing you with the skills to create successful applications and connecting you with the right organizations to make money from your work, continues. We have invited every .NET user group in the Carolinas to participate in this Code Camp. Never before have so many groups come together to present one single event. So mark your calendar and plan to join us on Saturday, October 10 at the Levine Campus of CPCC for the Carolina Mega Code Camp! More event details coming soon."

See the Charlotte Developers Guild website for more information:
http://www.developersguild.org

Adding Active Directory Users to SQL Server behind a Firewall

I ran into an issue the other day that I thought others might be interested in knowing. I built a new system and turned on Windows Firewall, then discovered that I couldn't add users from Active Directory to SQL Server. This server happens to be SQL Server 2005 w/SP3 on Windows Server 2003 R2 w/SP2, however this is not the important part. I did a little research on the ports used by SQL and Windows Server and found a bunch of information which is in a previous post, "Network Ports used by Microsoft Server Products". I ultimately decided to try a few key ports and determined that I needed to enable Port 445 for TCP traffic. Enabling TCP on Port 445 resolved my issue and I was able to add users from Active Directory once again. The port is listed under a default setting in the Windows Firewall Exceptions, it is under "File and Printer Sharing". Click on "File and Printer Sharing" and choose "Edit", then Enable Port 445. This should resolve the issue with adding Active Directory users to SQL Server.

Network Ports used by Microsoft Server Products

Here is some helpful information I stumbled upon this morning on the ports used for various Microsoft Server Products. At one time or another we've all tried to figure out the various ports used by different services to communicate between systems. Here is a list of some of the more common and some not so common ports used. Scroll to the bottom of the page to find a full list of the pieces mentioned in the support article.

http://www.microsoft.com/smallbusiness/support/articles/ref_net_ports_ms_prod.mspx

New New Blog...

I'm finally getting my new blog setup after urging from friends and colleagues to start blogging again. I'll have some new posts coming up soon on SQL Server optimization tweaks and some maintenance stuff I've been working on.

Keep an eye out for the Charlotte Mega Code Camp, it will be on October 10, 2009 and I'm working on a presentation or two to give at that. The event will be at the CPCC Levine Campus in Matthews, NC. When I finalize what I'll be speaking about I'll post some more info. Stay tuned for details and check out the Charlotte Enterprise Developers Guild website for info on the event. Charlotte Enterprise Developers Guild