SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
In SQL Server 2008, security is locked down further than previous versions. Two biggies are the fact that the local windows admins are not added to the SA role and the SA login is disabled by default. During setup, you manually add accounts to the SA role. You must add at least one. In my case, I added myself during setup.
I removed myself from the SA role to do some permissions testing. Yep, they worked. I could not do X or Y. Now, I try to login as SA to give myself SA again. Denied.
And no, I do not have permissions to enable SA because I am “that guy”. Now, it looks like I will be rebuilding the master db. Good thing this was a test box.
Disclaimer:
Just because you can does not mean you should and even if you should, use with care. For example, you will have no friends after a day or so of updating your facebook status every time a tlog backup runs on your farm. There are also many security concerns to consider. That said, I can think of a few uses for SQL Server Web 2.0 mashups and I am not all that creative.
How it works
Sign up at http://ping.fm and then sign up at whatever services you want to update from SQL. Here is a list of what they currently support. From SQL, we could send a message to twitter or even better, yammer via database mail. Here is the obligatory, “Don’t send anything confidential over email”
Possible Uses
Augmenting your monitoring – The keyword is word is “augmenting” except for the smallest installations. For the crown jewel of the company, you might get a call from the 24/7 staff and an email. Why not a tweet too especially if it can add SMS functionality that you may not currently have?
Turn X into an RSS feed – Where X could be, all job executions, any SEV error messages, any trace event or DDL operations, or [your idea here]. You can funnel the RSS into the intranet, your reader or [insert creative idea here].
An insert trigger on your press release table – Blast it to the world. Some companies are already doing similar things http://dell.com/twitter.
Personal updates from the SQL Editor – I don’t know about you but I have spent weeks on end in tsql. Some deadlines don’t allow for time to come up for air. It would pretty cool in a geeky way to tell the world that I just replaced 500 lines of cursor code with a 10 liner or whatever without leaving SSMS.
Example Code:
sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Database Mail XPs', 1;GORECONFIGUREGOEXECUTE msdb.dbo.sysmail_add_account_sp@account_name = 'Ping.fm demo', @description = 'Mail account', @email_address = 'jason@statisticsio.com', @replyto_address = 'jason@statisticsio.com', @display_name = 'Jason Massie', @mailserver_name = 'mail.statisticsio.com' ;-- Create a Database Mail profileEXECUTE msdb.dbo.sysmail_add_profile_sp@profile_name = 'MailAccount', @description = 'Profile used for administrative mail.' ;-- Add the account to the profileEXECUTE msdb.dbo.sysmail_add_profileaccount_sp@profile_name = 'MailAccount', @account_name = 'Ping.fm demo', @sequence_number =1 ;-- Grant access to the profile to all users in the msdb databaseEXECUTE msdb.dbo.sysmail_add_principalprofile_sp@profile_name = 'MailAccount', @principal_name = 'public', @is_default = 1 ;
-- here is the magic blasphemy. EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'I just updated twitter, linkedin, facebook, plurk, rejaw from SQL!
EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'
In closing
One more time, pay attention to security. There are a few commercial products on the horizon. http://ping.fm and most of these service also have API’s that may be a better alternative than database mail. Finally, database mail was meant for administrative alerts and I suggest you stick with that mindset..
Well not quite :) However, if you have a 3GB classic ASP code base(I have seen this.) that you just cannot spend the cycles to update to secure code, here is a nice stop gap: A web application firewall. There are a lot of them out there. Most are ISAPI filters. You basically configure them to deny sql keywords in the URL among other things.
It is not without cost like web server overhead, admin work and response time. You also could lock things down too much and take down your site. It is not 100% especially if it is not correctly configured. However, it probably beats the alternatives if you cannot snap your fingers and fix your code.
My security buddies recommend this open source product: AQTRONIX WebKnight - Application Firewall for Web Servers.
AQTRONIX WebKnight is an application firewall for IIS and other web servers and is released under the GNU General Public License. More particularly it is an ISAPI filter that secures your web server by blocking certain requests. If an alert is triggered WebKnight will take over and protect the web server. It does this by scanning all requests and processing them based on filter rules, set by the administrator. These rules are not based on a database of attack signatures that require regular updates. Instead WebKnight uses security filters as buffer overflow, SQL injection, directory traversal, character encoding and other attacks. This way WebKnight can protect your server against all known and unknown attacks. Because WebKnight is an ISAPI filter it has the advantage of working closely with the web server, this way it can do more than other firewalls and intrusion detection systems, like scanning encrypted traffic.
I suggest you research for yourself though.
All that said, secure application design is the ultimate solution.
The changes in SQL Server to prevent the issues described in MS08-40 are already included in Cumulative Update 7+ for SQL Server 2005 Service Pack 2. This was confirmed by a MSFT contact. To quote him:
If CU#7 or CU#8 has been installed for SQL 2005 SP2, then you do not need to apply the patch. The version of the patch is (last 4 digits ) 3233. CU#7 is 3239 and CU#8 is higher. Since the cumulative updates are indeed cumulative, CU#7 and CU#8 both have this security patch.
If CU#7 or CU#8 has been installed for SQL 2005 SP2, then you do not need to apply the patch.
The version of the patch is (last 4 digits ) 3233.
CU#7 is 3239 and CU#8 is higher.
Since the cumulative updates are indeed cumulative, CU#7 and CU#8 both have this security patch.
The OS may still need to be patched. SUS or Windows Update should be able to tell you that. Hopefully, this is the last SQL security post for a while. :)
One of the security vulnerabilities was discovered by iDefense. They released their public advisory today with more details here. Here is an excerpt:
Remote exploitation of an integer underflow vulnerability within Microsoft Corp.'s SQL Server could allow a remote attacker to execute arbitrary code with the privileges of the SQL Server.The vulnerability exists within the code responsible for parsing a stored backup file. A 32-bit integer value, representing the size of a record, is taken from the file and used to calculate the number of bytes to read into a heap buffer. This calculation can underflow, which leads to insufficient memory being allocated. The buffer is subsequently overfilled leading to an exploitable condition.
Remote exploitation of an integer underflow vulnerability within Microsoft Corp.'s SQL Server could allow a remote attacker to execute arbitrary code with the privileges of the SQL Server.
The vulnerability exists within the code responsible for parsing a stored backup file. A 32-bit integer value, representing the size of a record, is taken from the file and used to calculate the number of bytes to read into a heap buffer. This calculation can underflow, which leads to insufficient memory being allocated. The buffer is subsequently overfilled leading to an exploitable condition.
The Microsoft Security Vulnerability Research & Defense blog provided even more info later today. The full post is here. Lets look at 3 sentences from this post that tells us this is really only dangerous if you are not following best practices to begin with.
Your app has to be injectable and it has to be open to port 80. If that is true, you are probably already running SQL as a domain admin or SA has a blank password. ;)
You should still test and deploy the hotfix ASAP. Do you trust all of the variables? Your code, MS's code, the disclosure.... Even if you do, there are still 3 other undisclosed vulnerabilities so get to patching.
Well, it was a great run. SQL Server 2005's pristine security record is now tarnished. In fact, there has not been a new SQL Server security vulnerability since 2003. It looks like they took the SQL slammer lesson to heart.
"
This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.
1. Memory Page Reuse Vulnerability - CVE-2008-0085
An information disclosure vulnerability exists in the way that SQL Server manages memory page reuse. An attacker with database operator access who successfully exploited this vulnerability could access customer data.
2. Convert Buffer Overrun - CVE-2008-0086
A vulnerability exists in the convert function in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.
3. SQL Server Memory Corruption Vulnerability - CVE-2008-0107
A vulnerability exists in SQL Server that could allow an authenticated attacker to gain elevation of privilege. An attacker who successfully exploited this vulnerability could run code and take complete control of the system.
4. SQL Server Buffer Overrun Vulnerability - CVE-2008-0106
The full article can be found here.
Don't know what "Windows Internal Database" is? I didn't either. Here is the wikipedia entry. I wonder if Windows 2008 will get an upgrade when Katmai RTM's.
Update: The binaries have been released. SQL 2005 gets bumped to version 9.00.3233.00. The direct x86 and x64 SQL 2005 download is here. Refer to the full article for 7.0, 2000, and express editions.