Search results matching tags 'Administration' and 'Security'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Administration,Security&orTags=0Search results matching tags 'Administration' and 'Security'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Third Party Applications and Other Acts of Violence Against Your SQL Serverhttp://sqlblog.com/blogs/kevin_kline/archive/2014/08/12/third-party-applications-and-other-acts-of-violence-against-your-sql-server.aspxTue, 12 Aug 2014 18:36:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54785KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">I just got finished reading a great blog post from my buddy,&nbsp;Thomas LaRock (<a href="https://twitter.com/#!/SQLRockstar">t</a>&nbsp;|&nbsp;<a href="http://www.thomaslarock.com/">b</a>), in which he describes a useful personal policy he used to track changes made to his SQL Servers when installing third-party products. Note that I'm talking about line-of-business applications here - your inventory management systems and help desk ticketing apps. I'm not talking about monitoring and tuning applications since they, by their very nature, need a different sort of access to your back-end server resources. (Full disclosure: both Tom and I currently work for different&nbsp;<a title="Kevin's employer" href="http://sqlsentry.com/">tools vendors</a>. But we're both wearing our former enterprise DBA hats for this discussion).</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">You can read Tom's blog post, as well as download the T-SQL script which checks for the most common vendor red flags,&nbsp;<a href="http://thomaslarock.com/2014/08/know-installer-database/">HERE</a>. I strongly recommend that you read this post and utilize his script, or some similar technology such as&nbsp;<a title="MSDN article on SQL Server Policy-Based Management" href="http://msdn.microsoft.com/en-us/library/bb510667.aspx">Policy-Based Management</a>, to accomplish the same ends.&nbsp;I penciled in a quick comment on Tom's post. But as my comment began to lengthen, I realized it might make a nice supplement to his insights as a blog post of my own.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">So here's my addition to Tom's post:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;text-align:center;"><span><strong>Third-Party Applications Also Raise a Red Flag for Things They DO NOT&nbsp;CHANGE&nbsp;in Your SQL Server, BUT SHOULD.</strong></span></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">One area where I frequently regretted the need for vendor apps, when I worked as an enterprise DBA, was in security. Nothing sends quite as strong a message of "We don't really care enough to work hard on this application" like an&nbsp;<em><span style="text-decoration:underline;"><strong>app which&nbsp;uses only the</strong>&nbsp;</span><strong><span style="text-decoration:underline;">SA account for user access</span></strong></em>.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2014/08/computer-network-security.jpg"><img class="aligncenter size-medium wp-image-6157" alt="computer-network-security" width="300" height="225" style="display:block;margin-left:auto;margin-right:auto;" src="http://kevinekline.com/wp-content/uploads/2014/08/computer-network-security-300x225.jpg"></a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">This practice of using SA for a line-of-business application is so bad on so many levels. My experiences showed that most apps that used only SA to access the database had many other problems. First of all, you could be certain that security was the least of their concerns and that there'd be other flagrant breaches of database security best practices. But often, upon deeper inspection, I would discover that use of SA as the only account for an application has the harbinger of database design issues, lurking performance problems, and lousy code. That one red flag foretold&nbsp;of very bad things to come with that vendor's product.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">Now in case you were hiding in a cave and/or refused to ever read the news, I want to remind you that&nbsp;<a href="http://mashable.com/2014/08/05/russian-hacker-passwords/">SQL injection hacker attacks</a>&nbsp;are one of the most damaging of all hacks on the Internet. And much of the time, those SQL injection attack happen because of sloppy &nbsp;coding practices such as using SA for standard transaction processing. Surely you mean "sloppy security practices", Kevin? Nope. I mean&nbsp;<em>CODING</em>. The main reason these applications rely on SA is because the development team did not want to code a more robust authorization system. "Hey, let's give it ALL to the end-user. They know what they're doing, right?" Well, sometimes. But you can't count on that assumption. And you can also assume that bad people who are not users will want to break in to the application. Again, there's that word 'harbinger' again. It just sounds so fricken ominous, doesn't it? But I digress...</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">The next time you are face with the buy versus build decision and the executives choose to buy, make sure that the application DOES NOT USE SA for standard transactional data processing.&nbsp;For example, if your company installs a new help desk ticketing application,&nbsp;make sure the application comes with at least a distinct account for&nbsp;data readers, a data writer, and&nbsp;for super-users,&nbsp;FOR THAT ONE DATABASE.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">If you don't know whether an application and its backend database use SA or not, be sure to check. (Tom's script helps you do that). And if you're able to influence future buy vs build decisions, be sure to make this a sticking point. Nothing helps an application vendor clean up their act, technologically speaking, like telling them WHY they are losing your business. You'll make the world a better place.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">So what do you think? Are there other things about installing a vendor database that raise a red flag when they&nbsp;<em>do not change</em>? What are they? I'd love to hear your comments.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">Many thanks,</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;">-Kevin</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;"><a href="http://twitter.com/kekline">-Follow me on Twitter!</a><br><a href="https://plus.google.com/u/1/113032055249023350257?rel=author">-Google Author</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:19px;"><a href="https://plus.google.com/u/1/113032055249023350257?rel=author"></a><a style="font-size:13.333333969116211px;" href="http://kevinekline.com/">-More content at KevinEKline.com</a></p>High-Availability White Papers and Resources for SQL Serverhttp://sqlblog.com/blogs/kevin_kline/archive/2012/07/26/high-availability-white-papers-and-resources-for-sql-server.aspxThu, 26 Jul 2012 15:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44457KKline<div class="mceTemp" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><div class="mceTemp"><a rel="attachment wp-att-2011" href="http://kevinekline.com/2012/07/26/high-availability-white-papers-and-resources-for-sql-server/charlotte-sql-ug/"><img class="size-medium wp-image-2011" title="Charlotte SQL UG" alt="" width="300" height="168" style="border:0px none;cursor:default;margin:0px;padding:0px;-webkit-user-drag:none;" src="http://kevinekline.com/wp-content/uploads/2012/07/Charlotte-SQL-UG-300x168.jpg"></a>In foreground, attendee makes dreaded "shoot myself" hand sign to the speaker.</div><p>I was just telling the good people of Charlotte about how they (and how YOU) need to read all things by Paul Randal (<a title="Paul Randal's Blog" href="http://www.sqlskills.com/BLOGS/paul/">blog</a>&nbsp;|&nbsp;<a title="Paul Randal's Twitter Feed" href="http://twitter.com/paulrandal">twitter</a>), except for all of his&nbsp;<a title="Maybe He Did Write a Romance Novel, Maybe He Didn't" href="http://www.amazon.com/forum/romance?cdForum=FxM42D5QN2YZ1D&amp;cdThread=Tx2769ZA6OCU1BD">cheesy romance novels</a>&nbsp;like&nbsp;<a title="Quite Possibly The Worst Romance Novel EVER" href="http://www.amazon.com/Caress-and-Conquer-ebook/dp/B006IUV50A/ref=sr_1_2?ie=UTF8&amp;qid=1343317555&amp;sr=8-2&amp;keywords=Caress+and+Conquer+by+Connie+Mason"><em>Caress and Conquer</em></a>&nbsp;written under the nom de plum of Connie Mason.</p><p>There's lots more good stuff from Paul, just not romantic.</p><p>This is a 'so-last-version' whitepaper describing &nbsp;five common high-availability and disaster-recovery architectures deployed by customers, along with a case study of each. Although the white paper is specific to SQL Server 2008 R2 and isn't updated for AlwaysOn features, it's still really, really good. &nbsp;It covers:</p><ul><li>Failover Clustering for High Availability with Database Mirroring for Disaster Recovery</li><li>Database Mirroring for High Availability and Disaster Recovery</li><li>Geo-Clustering for High Availability and Disaster Recovery</li><li>Failover Clustering for High Availability Combined with SAN-Based Replication for Disaster Recovery</li><li>Peer-to-Peer Replication for High Availability and Disaster Recovery</li></ul><p>You can get it from&nbsp;<a href="http://download.microsoft.com/download/5/B/D/5BD13FFA-5E34-4AE1-9AA0-C6E6951B8FC8/SQL%20Server%202008%20R2%20High%20Availability%20Architecture%20White%20Paper.docx">this link</a>. &nbsp;Not everything is transferable to new AlwaysOn technologies, but then again AlwaysOn is an Enterprise Edition feature. &nbsp;So the database mirroring recommendation can be upsized, in many if not all cases, to SQL Server 2012, while the SAN and peer-to-peer recommendations continue to hold fast.</p><p>In addition, I encourage you to get up to speed on AlwaysOn. &nbsp;There are two great AlwaysOn FAQs that I recommend.&nbsp; The first is Microsoft’s official AlwaysOn FAQ at&nbsp;<a href="http://msdn.microsoft.com/en-us/sqlserver/gg508768.aspx">http://msdn.microsoft.com/en-us/sqlserver/gg508768.aspx</a>.&nbsp; The second comes from my buddy and high-availability expert Allan Hirt (<a title="Allan Hirt, Mr. SQLHA" href="http://www.sqlha.com/">blog</a>&nbsp;|&nbsp;<a title="Allan Hirt's Twitter Feed" href="http://twitter.com/sqlha">twitter</a>) at&nbsp;<a href="http://www.sqlha.com/2012/04/13/allans-alwayson-availability-groups-faq/">http://www.sqlha.com/2012/04/13/allans-alwayson-availability-groups-faq/</a>.</p><p>To get started with AlwaysOn, check out&nbsp;<a href="http://msdn.microsoft.com/en-us/library/cc645581.aspx">http://msdn.microsoft.com/en-us/library/cc645581.aspx</a>.</p><p>Enjoy,</p><p>-Kev</p></div>Microsoft Document Watch for Operational Excellencehttp://sqlblog.com/blogs/kevin_kline/archive/2011/08/08/microsoft-document-watch-for-operational-excellence.aspxMon, 08 Aug 2011 19:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:37621KKlineBack when my day-to-day duties included database administration work and enterprise architecture, I became rather obsessed with the idea of <em>operational excellence</em>. I read everything I could on the topic. I made a list of favorites, which became somewhat shabby over time, as I dog-eared important pages and scribbled notes in the margins. (Perhaps that list of favorites might, in and of itself, make a good blog post). Fast-forward a decade and I'm still mightily interested in operational excellence for IT organizations. It's just that <em>so much </em>good material is available for free on the web.
Here's a run-down of several useful documents and downloads to improve overall operation performance for those of you in a Microsoft-centric IT organization:
<h3>Microsoft Operations Framework</h3>
<a href="http://www.microsoft.com/downloads/details.aspx?displaylang=en&amp;familyid=457ed61d-27b8-49d1-baca-b175e8f54c0c" title="Microsoft Operations Framework (MOF)" target="_blank"><em>Microsoft Operations Framework</em> (MOF) version 4.0 guide</a> is practical guidance for IT organizations. With the release of version 4.0, MOF now reflects a single, comprehensive IT service lifecycle—it helps IT professionals connect service management principles to everyday IT tasks and activities and ensures alignment between IT and the business.
<h3>Infrastructure Planning and Design</h3>
The <a href="http://www.microsoft.com/downloads/details.aspx?displaylang=en&amp;familyid=ad3921fb-8224-4681-9064-075fdf042b0c" title="Microsoft Infrastructure Planning and Design Guide" target="_blank"><em>Infrastructure Planning and Design</em> (IPD) guides</a> are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.
<h3>Microsoft Baseline Security Analyzer 2.2 (for IT Professionals)</h3>
The <em><a href="http://www.microsoft.com/downloads/details.aspx?displaylang=en&amp;familyid=02be8aee-a3b6-4d94-b1c9-4b1989e0900c" title="Microsoft Baseline Security Analyzer" target="_blank">Microsoft Baseline Security Analyzer</a></em> provides a streamlined method to identify missing security updates and common security misconfigurations. MBSA 2.2 is a minor upgrade correct minor issues and add optional catalog support.
<h3>Security Compliance Manager</h3>
The <a href="http://www.microsoft.com/downloads/details.aspx?displaylang=en&amp;familyid=5534bee1-3cad-4bf0-b92b-a8e545573a3e" title="Microsoft Security Compliance Manager" target="_blank">Microsoft Security Compliance Manager</a> provides centralized security baseline management features, a baseline portfolio, customization capabilities, and security baseline export flexibility to accelerate your organization’s ability to efficiently manage the security and compliance process for the most widely used Microsoft technologies.More than one way to skin an Audithttp://sqlblog.com/blogs/buck_woody/archive/2010/05/20/more-than-one-way-to-skin-an-audit.aspxThu, 20 May 2010 13:40:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:25343BuckWoody<P>I get asked quite a bit about auditing in SQL Server. By "audit", people mean everything from tracking logins to finding out exactly who ran a particular SELECT statement. </P>
<P>In the really early versions of SQL Server, we didn't have a great story for very granular audits, so lots of workarounds were suggested. As time progressed, more and more audit capabilities were added to the product, and in typical database platform fashion, as we added a feature we didn't often take&nbsp;the others away. So now, instead of not having an option to audit actions by users, you might face the opposite problem - too many&nbsp;ways to audit! You can read more about the options you have for tracking users here: <A href="http://msdn.microsoft.com/en-us/library/cc280526(v=SQL.100).aspx">http://msdn.microsoft.com/en-us/library/cc280526(v=SQL.100).aspx</A>&nbsp;</P>
<P>In SQL Server 2008,&nbsp;we introduced SQL Server Audit, which uses Extended Events to really get a simple way to&nbsp;implement high-level or granular auditing.&nbsp;You can read more about that here: <A href="http://msdn.microsoft.com/en-us/library/dd392015.aspx">http://msdn.microsoft.com/en-us/library/dd392015.aspx</A>&nbsp;</P>
<P>As with any feature, you should understand what your needs are first. Auditing isn't "free" in the performance sense, so you need to make sure you're only auditing what you need to.</P>Backup those keys, citizenhttp://sqlblog.com/blogs/buck_woody/archive/2010/04/20/backup-those-keys-citizen.aspxTue, 20 Apr 2010 12:14:50 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:24408BuckWoody<p>Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.</p> <p>The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the <a href="http://msdn.microsoft.com/en-us/library/ms190337.aspx" target="_blank">BACKUP SERVICE MASTER KEY command, which you can read more about here</a>.</p> <p>The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the <a href="http://technet.microsoft.com/en-us/library/ms174387.aspx" target="_blank">BACKUP MASTER KEY command, which you can read more about here</a>.</p> <p>Finally, you can use the keys to create certificates and other keys – those should also be backed up. <a href="http://msdn.microsoft.com/en-us/library/ms189586.aspx" target="_blank">Read more about those here</a>.</p> <p>Anyway, the important part here is the backup. Make sure you keep those keys safe!</p>Have you backed up your keys lately?http://sqlblog.com/blogs/buck_woody/archive/2010/03/01/have-you-backed-up-your-keys-lately.aspxMon, 01 Mar 2010 14:06:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:22679BuckWoody<p>Did you know that you already have a Server Master Key (SMK) generated for your system? That’s right – while a Database Master Key (DMK) is generated when you encrypt a certificate or Asymmetric Key with code, the Server Master Key is generated automatically when you start the Instance. </p> <p>So you should back all of those keys up periodically, and then store that backup AWAY from the server itself. </p> <p>There are two reasons for this – first, if the drives get stolen and you’re storing the key backup there, well, that should be obvious why that’s bad. Second, you want to protect the keys in case the system is destroyed or you can’t recover the drives. You will need those keys if you have encrypted anything in the database to get the data back.</p> <p>More here: <a href="http://technet.microsoft.com/en-us/library/bb964742.aspx">http://technet.microsoft.com/en-us/library/bb964742.aspx</a>&#160;</p> <p>No, the standard Maintenance Wizards don’t get this data. And no, I haven’t seen it addressed in most of the maintenance scripts out there anyway – sometimes for good reason, but this means you need to take care of it manually, and then document where you put that backup.</p>