Buck Woody : Securityhttp://sqlblog.com/blogs/buck_woody/archive/tags/Security/default.aspxTags: SecurityenCommunityServer 2.1 SP2 (Build: 61129.1)DevOps for Windows Azurehttp://sqlblog.com/blogs/buck_woody/archive/2013/03/12/devops-for-windows-azure.aspxTue, 12 Mar 2013 15:29:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48210BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/48210.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=48210<p>"DevOps" (Short for<strong> Dev</strong>eloper <strong>Op</strong>eration<strong>s</strong>) is one of a group of new terms such as "Cloud", "Big Data" and "Data Scientist" - words that are somewhere between marketing and tasks we've actually had around in other forms for years.However, working in a Distributed Environment (Both on and off premises)&nbsp; like Windows Azure does bring a new set of tasks to the operations we currently perform in Information Technology.</p>
<p>Before I offer some guidance here, I need to carefully define the term "DevOps" as I use it.There are other definitions that involve Application Lifecycle Management (ALM) and standard operations policies, and you're free to use those as well, but this is the definition I'll use for this post: &nbsp;By DevOps I mean <em>those tasks involved with deploying, managing and monitoring a Windows Azure (or hybrid) project</em>.</p>
<p>Another caveat: This is a non-authoritative, non-comprehensive post. I'll include only an outline of the major tasks, not a complete manual on the topic. There's enough knowledge needed on this topic for at least a whitepaper or two, and perhaps even a book, but for the moment I wanted to get some information out to ensure you have something to work from until those come along.This is primarily a list of resources for a DevOps team.</p>
<p>With all of those caveats in mind, we'll start the discussion after the project is conceived and architected. In most cases the DevOps team (whether that is a dedicated team or simply part of what the current IT Ops team does) is also involved in the design, at least from an information point of view. There's a great overview of the entire process available in poster form here:&nbsp;<a href="http://www.microsoft.com/en-us/download/details.aspx?id=36837" target="_blank">http://www.microsoft.com/en-us/download/details.aspx?id=36837 </a>And you should also read this complete manual in preparation here: <a href="http://msdn.microsoft.com/en-us/library/hh871440.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/hh871440.aspx </a></p>
<h1>Deployment</h1>
<p>The first task after the design of the project is deployment. The deployment method depends on the type of solution; Windows Azure has the ability to run VM's, software code, or provide services that are already created (such as Active Directory).&nbsp;</p>
<h2>IaaS</h2>
<h3>Deploying Virtual Machines:</h3>
<p>Manually from the Portal:<a href="http://go.microsoft.com/fwlink/?linkid=254427&amp;amp;clcid=0x409" target="_blank"> http://go.microsoft.com/fwlink/?linkid=254427&amp;clcid=0x409</a></p>
<p>Through Scripting: <a href="https://www.windowsazure.com/en-us/downloads/?fb=en-us" target="_blank">https://www.windowsazure.com/en-us/downloads/?fb=en-us</a>,&nbsp;<a href="http://msdn.microsoft.com/en-us/library/ee460812.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ee460812.aspx </a></p>
<p>Copying your own VM's to Windows Azure: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/gg465385.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/gg465385.aspx</a>&nbsp;</p>
<p>Using System Center: <a href="http://www.techrepublic.com/blog/datacenter/deploy-an-on-premise-vm-to-windows-azure-with-app-controller/5919" target="_blank">http://www.techrepublic.com/blog/datacenter/deploy-an-on-premise-vm-to-windows-azure-with-app-controller/5919</a>&nbsp;</p>
<p>Virtual Networking: http://msdn.microsoft.com/en-us/library/windowsazure/jj156075.aspx, <a href="http://channel9.msdn.com/Shows/Cloud+Cover/Episode-88-Tips-and-Tricks-for-Windows-Azure-Virtual-Machines-and-Virtual-Networks">http://channel9.msdn.com/Shows/Cloud+Cover/Episode-88-Tips-and-Tricks-for-Windows-Azure-Virtual-Machines-and-Virtual-Networks, </a></p>
<h3>PaaS</h3>
<p>Through Visual Studio: <a href="http://www.microsoft.com/BizSpark/Azure/HowToDeployAzureApp.aspx" target="_blank">http://www.microsoft.com/BizSpark/Azure/HowToDeployAzureApp.aspx</a></p>
<p>Using CSPack: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/gg432988.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/gg432988.aspx</a></p>
<p>Through Scripting: <a href="https://www.windowsazure.com/en-us/downloads/?fb=en-us" target="_blank">https://www.windowsazure.com/en-us/downloads/?fb=en-us</a>, <a href="http://msdn.microsoft.com/en-us/library/ee460812.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ee460812.aspx</a></p>
<h3>SaaS</h3>
<p>Manually from the Portal:<a href="https://datamarket.azure.com/" target="_blank">https://datamarket.azure.com/</a></p>
<p>Through Scripting: <a href="https://www.windowsazure.com/en-us/downloads/?fb=en-us" target="_blank">https://www.windowsazure.com/en-us/downloads/?fb=en-us</a></p>
<h1>Monitoring</h1>
<p>Monitoring the system after deployment involves watching the availability and uptime of the system, along with security intrusions and tracking access through code.</p>
<h2>Health</h2>
<p>Using MetricsHub: <a href="http://channel9.msdn.com/Shows/Cloud+Cover/Episode-102-Using-MetricsHub-to-Monitor-Your-Windows-Azure-Applications" target="_blank">http://channel9.msdn.com/Shows/Cloud+Cover/Episode-102-Using-MetricsHub-to-Monitor-Your-Windows-Azure-Applications </a></p>
<p>Uptime and Availability through the Portal: <a href="http://www.windowsazure.com/en-us/support/service-dashboard/" target="_blank">http://www.windowsazure.com/en-us/support/service-dashboard/ </a></p>
<p>Uptime and Availability through Third Party Vendors: <a href="http://www.paraleap.com/AzureWatch" target="_blank">http://www.paraleap.com/AzureWatch</a>, <a href="http://sqlblog.com/b/buckwoody/archive/2012/07/03/management-and-monitoring-tools-for-windows-azure.aspx" target="_blank">http://blogs.msdn.com/b/buckwoody/archive/2012/07/03/management-and-monitoring-tools-for-windows-azure.aspx</a></p>
<p>Automatic Notification: <a href="http://www.codeproject.com/Articles/375892/Adding-SMS-notifications-to-your-Windows-Azure-pro" target="_blank">http://www.codeproject.com/Articles/375892/Adding-SMS-notifications-to-your-Windows-Azure-pro</a></p>
<h2>Performance</h2>
<p>Performance Counters: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/hh411520.aspx&amp;nbsp;" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/hh411520.aspx&nbsp;</a></p>
<p>Logging Diagnostics PaaS: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/gg433048.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/gg433048.aspx </a></p>
<p>Internal Instrumentation for PaaS: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/hh674491%28v=vs.103%29.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/hh674491%28v=vs.103%29.aspx</a>&nbsp;</p>
<p>Third Party Performance Testing: <a href="http://www.neustar.biz/enterprise/web-performance" target="_blank">http://www.neustar.biz/enterprise/web-performance</a>, <a href="http://sqlblog.com/b/buckwoody/archive/2012/07/03/management-and-monitoring-tools-for-windows-azure.aspx" target="_blank">http://blogs.msdn.com/b/buckwoody/archive/2012/07/03/management-and-monitoring-tools-for-windows-azure.aspx</a></p>
<h2>Costs</h2>
<p>Understanding Costs: <a href="http://msdn.microsoft.com/en-us/library/ff803372.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff803372.aspx</a>, <a href="http://technet.microsoft.com/en-us/magazine/gg213848.aspx" target="_blank">http://technet.microsoft.com/en-us/magazine/gg213848.aspx</a></p>
<p>Subscription Management: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/gg465713.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/gg465713.aspx</a>&nbsp;</p>
<p>System Center: <a href="http://technet.microsoft.com/en-us/library/hh221354.aspx" target="_blank">http://technet.microsoft.com/en-us/library/hh221354.aspx </a></p>
<p>Third-Party Tools: <a href="http://sqlblog.com/b/buckwoody/archive/2012/07/03/management-and-monitoring-tools-for-windows-azure.aspx" target="_blank">http://blogs.msdn.com/b/buckwoody/archive/2012/07/03/management-and-monitoring-tools-for-windows-azure.aspx</a></p>
<p>Example of listing your deployments: <a href="http://msdn.microsoft.com/en-us/library/gg651127.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/gg651127.aspx </a></p>
<h1>Management</h1>
<p>Managing the deployment involves Security, Upgrades, Troubleshooting, and High-Availability/Disaster Recovery.</p>
<p>Windows Azure Management Portal:&nbsp;<a href="http://www.windowsazure.com/en-us/" target="_blank">http://www.windowsazure.com/en-us/</a>&nbsp;</p>
<p>Management API's: <a href="https://www.windowsazure.com/en-us/downloads/?fb=en-usandhttp://msdn.microsoft.com/en-us/library/ee460812.aspx" target="_blank">https://www.windowsazure.com/en-us/downloads/?fb=en-us and http://msdn.microsoft.com/en-us/library/ee460812.aspx</a>,&nbsp;<a href="http://www.packtpub.com/sites/default/files/2220-chapter-7-managing-hosted-services-with-the-service-management-api.pdf?utm_source=packtpub&amp;amp;utm_medium=free&amp;amp;utm_campaign=pdf" target="_blank">http://www.packtpub.com/sites/default/files/2220-chapter-7-managing-hosted-services-with-the-service-management-api.pdf?utm_source=packtpub&amp;utm_medium=free&amp;utm_campaign=pdf </a></p>
<h2>Security</h2>
<p>Security Trust Center: <a href="http://www.windowsazure.com/en-us/support/trust-center/" target="_blank">http://www.windowsazure.com/en-us/support/trust-center/ </a></p>
<p>Working with Windows Azure Active Directory: <a href="http://sqlblog.com/b/windowsazure/archive/2012/11/28/windows-azure-now-supports-federation-with-windows-server-active-directory.aspx" target="_blank">http://blogs.msdn.com/b/windowsazure/archive/2012/11/28/windows-azure-now-supports-federation-with-windows-server-active-directory.aspx</a></p>
<p>Windows Azure Authentication: <a href="http://sqlblog.com/controlpanel/blogs/posteditor.aspx><strong></strong>http:/www.asp.net/vnext/overview/fall-2012-update/windows-azure-authentication" target="_blank"><strong></strong>http://www.asp.net/vnext/overview/fall-2012-update/windows-azure-authentication</a></p>
<p>Deploying a secure ASP.NET MVC application with OAuth: <a href="http://sqlblog.com/b/webdev/archive/2013/03/12/deploy-a-secure-asp-net-mvc-application-with-oauth-membership-and-sql-database.aspx" target="_blank">http://blogs.msdn.com/b/webdev/archive/2013/03/12/deploy-a-secure-asp-net-mvc-application-with-oauth-membership-and-sql-database.aspx </a></p>
<h2>Upgrades</h2>
<p>ALM Process for PaaS: <a href="http://sqlblog.com/blogs/buck_woody/archive/2011/01/25/windows-azure-use-case-agility.aspx" target="_blank">http://sqlblog.com/blogs/buck_woody/archive/2011/01/25/windows-azure-use-case-agility.aspx</a>&nbsp;</p>
<h2>Troubleshooting</h2>
<p>Windows Azure Support: <a href="http://www.windowsazure.com/en-us/support/contact/" target="_blank">http://www.windowsazure.com/en-us/support/contact/ </a></p>
<p>Upgrade and Fault Domains: <a href="http://blog.toddysm.com/2010/04/upgrade-domains-and-fault-domains-in-windows-azure.html" target="_blank">http://blog.toddysm.com/2010/04/upgrade-domains-and-fault-domains-in-windows-azure.html </a></p>
<h2>HADR</h2>
<p>Load-Balancing Endpoints for IaaS: <a href="http://www.windowsazure.com/en-us/manage/windows/common-tasks/how-to-load-balance-virtual-machines/" target="_blank">http://www.windowsazure.com/en-us/manage/windows/common-tasks/how-to-load-balance-virtual-machines/ </a></p>
<p>Extending SQL Server HADR to Windows Azure: <a href="http://sqlblog.com/b/buckwoody/archive/2013/01/08/microsoft-windows-azure-disaster-recovery-options-for-on-premises-sql-server.aspx" target="_blank">http://blogs.msdn.com/b/buckwoody/archive/2013/01/08/microsoft-windows-azure-disaster-recovery-options-for-on-premises-sql-server.aspx</a>&nbsp;</p>
<p>HADR for IaaS: <a href="http://www.visionsolutions.com/" target="_blank">http://www.visionsolutions.com/,&nbsp;http://blogs.technet.com/b/windowsserver/archive/2012/03/28/microsoft-online-backup-service.aspx </a></p>
<p>Multiple Instances for PaaS: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/ee871996.aspx&amp;nbsp;" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/ee871996.aspx&nbsp;</a></p>
<p>Business Continuity for Windows Azure: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/hh873027.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/windowsazure/hh873027.aspx</a>,&nbsp;<a href="http://sqlblog.com/b/avkashchauhan/archive/2011/10/14/windows-azure-vm-downtime-due-to-host-and-guest-os-update-and-how-to-manage-it-in-multi-instance-windows-azure-application.aspx" target="_blank">http://blogs.msdn.com/b/avkashchauhan/archive/2011/10/14/windows-azure-vm-downtime-due-to-host-and-guest-os-update-and-how-to-manage-it-in-multi-instance-windows-azure-application.aspx </a></p>
<h1>Disposition</h1>
<p>When the project is complete, you'll need to remove the VM's in IaaS, or data and code from PaaS and shut down the deployment. Prior to doing that, you should:</p>
<ol>
<li>Copy all data from the deployment to a local repository</li>
<li>Document the process</li>
<li>Notify Microsoft of your intent to stop the project to work with your representative on billing matters</li>
</ol>
<p>The primary tool for disposal is the Windows Azure Portal.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=48210" width="1" height="1">DevOpsManagementSecurityWindows AzureThe Importance of Paranoia for the Technical Professionalhttp://sqlblog.com/blogs/buck_woody/archive/2012/08/08/the-importance-of-paranoia-for-the-technical-professional.aspxWed, 08 Aug 2012 12:19:11 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44620BuckWoody1http://sqlblog.com/blogs/buck_woody/comments/44620.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=44620<p>I recently read a blog post from a technical professional who&rsquo;s account had been hacked (<a href="http://www.wired.com/gadgetlab/2012/08/apple-amazon-mat-honan-hacking/all/">http://www.wired.com/gadgetlab/2012/08/apple-amazon-mat-honan-hacking/all/</a>)&nbsp;&nbsp;&ndash; not because he used poor passwords or unsafe practices, but because the hackers used some social engineering to get around the safety he had put into place.&nbsp;</p>
<p>While I won&rsquo;t focus on the particulars of his situation, the interesting part of his loss was the fragility of the security of his data. In this case, he lost personal data &ndash; with no way to replace it. Two things stood out for me in his article: the chain of security through his accounts, and the single-source of data he had.</p>
<p>In this case, someone contacted the vendor and pretended to be this person. Using easily obtained information, they simply gained access to the account, and didn&rsquo;t even have to hack the password. From there, the chain was that using various convenience-features, the hackers could delete the smartphone, and then on to the laptop the person owned. They completely wiped that out, and this is where there is an issue &ndash; he had his data on that laptop, and on the same vendor&rsquo;s cloud backup. Since the hacker *<b>was</b>* the account owner by that time, they wiped out both. The person&rsquo;s personal pictures, etc were gone forever. From there the hackers impersonated the person on Twitter and made racist and other statements to embarrass the person.</p>
<p>Although lots of features are available in all vendor products, I&rsquo;ve always been&hellip;.paranoid about using them. I try to follow the &ldquo;moats and bridges&rdquo; approach to security, meaning that one account or feature doesn&rsquo;t lead to another. I don&rsquo;t link things together that can be used to attach to more than one account, even when it's a cool new feature. One public logon from an airport&rsquo;s &ldquo;free&rdquo; wifi (which I never use, by the way) can lead to these attacks &ndash; even if you don&rsquo;t think you&rsquo;re logging on. Ever check your mail from the airport? Do you have more than one mail account in your mail client? You could be hacked. I realize most client software does a good job of trying to prevent this, but I use my own MiFi device which I have set to the highest encryption I can.</p>
<p>I also keep lots of data in the cloud &ndash; but that&rsquo;s not the only place. Periodically I have my important data backed up to a local drive,which I rotate to another secure location. After all, I&rsquo;ve moved most of my books, pictures, scans, everything to a digital format. There&rsquo;s no way I&rsquo;m keeping that in just one place, or on just one vendor.&nbsp;</p>
<p>There are other things you can do to protect yourself &ndash; a great list is here: <a href="http://gizmodo.com/5932663/9-things-you-absolutely-must-do-to-keep-your-online-identity-secure">http://gizmodo.com/5932663/9-things-you-absolutely-must-do-to-keep-your-online-identity-secure</a></p>
<p>When I help clients design solutions on Windows Azure,&nbsp;I recommend another copy of the storage wherever possible &ndash; even on other vendor's cloud storage or locally on a drive, or both. I&rsquo;m paranoid that way &ndash; I don&rsquo;t want them to lose data. We take extraordinary precautions against losing data. Azure data has three copies on separate fault domains, and then those three are copied again to another physical datacenter automatically, that&rsquo;s just built into the system. Even so, I&nbsp; recommend periodic backups to other<br />locations of data the client can&rsquo;t easily re-generate.</p>
<p>While we provide lots of tools, information and guidance about security and protection in Windows Azure, ultimately it's up to you to properly secure your assets and plan for disaster recovery. That's true of any cloud provider - you need to learn the platform well to understand how to protect your data.</p>
<p>What I architect in Windows Azure I practice at home. Read that blog post, and I think you will agree it&rsquo;s good to be a little paranoid. Sometimes they really are out to get you.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=44620" width="1" height="1">AzureBest PracticesCloudCloud ComputingSecuritySQL Azure and Trust Serviceshttp://sqlblog.com/blogs/buck_woody/archive/2012/03/27/sql-azure-and-trust-services.aspxTue, 27 Mar 2012 12:32:17 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42507BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/42507.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=42507<p>Microsoft is working on a new Windows Azure service called &ldquo;Trust Services&rdquo;. Trust Services takes a certificate you upload and uses it to encrypt and decrypt sensitive data in the cloud. Of course, like any security service, there&rsquo;s a bit more to it than that. I&rsquo;ll give you a quick overview of how you can use this product to protect data you send to SQL Azure.</p>
<p>The primary issue with storing data in the cloud is that you are in an environment that isn&rsquo;t under your control &ndash; in fact, that&rsquo;s the benefit of being in a distributed computing environment in the first place. On premises you&rsquo;re able to encrypt data you don&rsquo;t want anyone else to see, using various methods such as passwords (not very strong) or certificates (stronger). When you use a certificate, it&rsquo;s vital that you create (or procure) and protect it yourself.</p>
<p>When you store data remotely, regardless of IaaS, PaaS or SaaS, you don&rsquo;t own the machines where the data lives. That means if you use a certificate from the cloud vendor to encrypt the data, you have to trust that the data won&rsquo;t be accessed by the vendor. In some cases having a signed agreement with the vendor that they won&rsquo;t access your data is sufficient, in other cases that doesn&rsquo;t meet the requirements your system has for security.</p>
<p>With the new Trust Services service, the basic process is that you use a Portal to create a Trust Server using&nbsp;policies and other controls. You place a X.509 Certificate you create or procure in that server. Using the Software&nbsp;development Kit (SDK), the developer has access to an Application Layer Encryption Framework to set fields of data they want to encrypt. From there, the data can be stored in SQL Azure as a standard field &ndash; only it is encrypted before it ever arrives. The portion of the client software that decrypts the data uses the same service, so the authenticated user sees the data if they are allowed to do so. The data remains encrypted &ldquo;at rest&rdquo;.&nbsp;</p>
<p><a href="http://sqlblog.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-79/2625.TrustServices1.png"><img alt="" src="http://sqlblog.com/resized-image.ashx/__size/550x0/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-79/2625.TrustServices1.png" border="0" /></a></p>
<p>You can learn more about this product and check it out in the SQL Azure labs at <a href="http://www.microsoft.com/en-us/sqlazurelabs/labs/trust-services.aspx">Microsoft Codename "Trust Services"</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=42507" width="1" height="1">SecuritySQL AzureSQL Azure - Requiring Encrypt=Truehttp://sqlblog.com/blogs/buck_woody/archive/2012/03/06/sql-azure-requiring-encrypt-true.aspxTue, 06 Mar 2012 13:43:11 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42122BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/42122.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=42122<p><em><font color="#c0504d">(Many thanks to Peter Gvozdjak and Dan Benediktson here at Microsoft who worked with me on this issue and provided the bulk of information for this post)</font></em></p> <p>Recently I had a customer inquire about some performance tuning he wanted to do for SQL Azure, and as part of that he found that it was possible to remove the “<strong>Encrypt=True</strong>” setting on <a href="http://msdn.microsoft.com/en-us/library/ee336243.aspx" target="_blank">the ADO.NET connection to SQL Azure</a>. We have always stated that the connections to SQL Azure are encrypted, so being able to remove this string surprised him. (More on that reference here: <a href="http://msdn.microsoft.com/en-us/library/windowsazure/ff394108.aspx">http://msdn.microsoft.com/en-us/library/windowsazure/ff394108.aspx</a>) </p> <p>It is true that all connections to SQL Azure are encrypted - whether you use the <strong>Encrypt=True </strong>string or not. We’ll force the connection to encrypt even if you don’t, or we won’t route it. However, you do want to use that string, for a couple of reasons. </p> <p>Whenever you include the <strong>Encrypt=True </strong>string, the connection will require that your client validate the Certificate that SQL Azure presents, to ensure that key is the one used by Microsoft. If you don’t include that string, it’s possible - not probable, but possible - that someone could set up a false DNS to cause your certificate to be validated elsewhere. </p> <p>So don’t give the bad guys a way in - there is no performance gain (other than perhaps if the bad DNS is in your own building!) by leaving it off. Follow the best practice of using <strong>Encrypt=True</strong>.</p> <p>There’s more on connection management for things like retries and so on here: <a href="http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx">http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=42122" width="1" height="1">AzureSecuritySQL AzureShould All Data Be Encrypted By Default?http://sqlblog.com/blogs/buck_woody/archive/2011/08/09/should-all-data-be-encrypted-by-default.aspxTue, 09 Aug 2011 13:45:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:37638BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/37638.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=37638<p>Recently several IT industry information outlets have reported that there has been a 10-year concentrated, organized effort on breaking through computer security at some of the largest companies in the world. Government sites have also been attacked in multiple countries. Add to this the regular loss of data by banking and other industries, and the fear of “the cloud” as a storage location, and it seems to beg the question asked in the title in this post: “should all data, everywhere, be encrypted by default?” </p> <p>If you’re new to encryption, there’s an excellent video and overview here: <a href="http://blogs.msdn.com/b/plankytronixx/archive/2010/10/23/crypto-primer-understanding-encryption-public-private-key-signatures-and-certificates.aspx">http://blogs.msdn.com/b/plankytronixx/archive/2010/10/23/crypto-primer-understanding-encryption-public-private-key-signatures-and-certificates.aspx</a>&#160;</p> <p>If all data were encrypted, the break-in to websites would still continue, but the value would be lessened for some types of “orthogonal” attacks that only seek the pure stream of data. </p> <p><strong>Data States</strong></p> <p>Computing has two major components - static program elements and data. The program doesn’t change (until it is updated, of course) over the course of a transaction between a user and the ultimate data store. Data is classified as anything that is manipulated by the program. That implies three states of the data interchange: Creation, Transmission, and Storage. In on-premise systems, many times none of these states are encrypted. The entire system from user to data store is viewed as “secure”, which of course evidence has proved it is not. In some cases, even laptops are viewed as part of an on-premise system, and so is left unprotected. If all data were treated as “publicly viewable”, that mindset would lead to encrypting the data at all states, even for on-premise systems.</p> <p><em>Creation</em></p> <p>In this phase, a user, device or other input program creates data to send to the program. This can be entries on a web form, input from a weather sensor, or one service (program) sending information to another service. There are multiple ways to encrypt data at this state, most notably using client-side libraries such as the Windows Crypto API, hardware encryption and others. The reference for the Crypto API is here: <a href="http://msdn.microsoft.com/en-us/library/ms867086.aspx">http://msdn.microsoft.com/en-us/library/ms867086.aspx</a></p> <p><em>Transmission</em></p> <p>After the data is created, it needs to be transmitted to the processing and storage system. the references above explain how to secure the communications channel between the client systems and the various components used within the system. In the case of Windows Azure, the session can be protected with a secure session, and all communications within the Azure datacenters are encrypted. The key is that the transmission of data, regardless of method, should be considered to be “in the clear”, and treated as such. Without the decryption algorithm, it’s much harder to get to the ultimate goal. </p> <p><em>Storage (data at rest) </em></p> <p>It follows that f the data is encrypted at the source, and the decryption method is retained only with the code that processes the data, then the data “at rest” if obtained is less accessible. If the data is not encrypted at the source, then this step should be put into place at a minimum. In many cloud systems, including Windows and SQL Azure, the data is not encrypted at rest. There are various reasons for this, including performance, physical and logical security already in place, and the fact that the encryption process would expose customer data to the provider while it is being encrypted. In this case, the key is to encrypt the data before it is transmitted and stored, so that it is encrypted ahead of time. </p> <p><strong>Considerations</strong></p> <p>Encrypting data is a separate process, and must be factored into the original codebase. This means additional effort, and more CPU power for the encryption process (although many systems have security hardware included which help with this) and of course protecting the keys. If the keys are accessed, the data is considered unencrypted from then on, and all previous encryption with that particular key is now vulnerable. Key rotation and protection is essential. Even so, the benefits of treating all data as being at risk outweighs the efforts.</p> <p>You can learn more about general encryption here: <a href="http://msdn.microsoft.com/en-us/library/aa380255(VS.85).aspx">http://msdn.microsoft.com/en-us/library/aa380255(VS.85).aspx</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=37638" width="1" height="1">AzureBest PracticesCloudCloud ComputingDataEncryptionSecuritySQL AzureWindows AzureOnline Password Security Tacticshttp://sqlblog.com/blogs/buck_woody/archive/2010/12/14/online-password-security-tactics.aspxTue, 14 Dec 2010 14:11:24 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:31626BuckWoody3http://sqlblog.com/blogs/buck_woody/comments/31626.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=31626<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Recently two more large databases were attacked and compromised, one at the popular Gawker Media sites and the other at McDonald&rsquo;s. Every time this kind of thing happens (which is FAR too often) it should remind the technical professional to ensure that they secure their systems correctly. If you write software that stores passwords, it should be heavily encrypted, and not human-readable in any storage. I advocate a different store for the login and password, so that if one is compromised, the other is not. I also advocate that you set a bit flag when a user changes their password, and send out a reminder to change passwords if that bit isn&rsquo;t changed every three or six months.<span style="mso-spacerun:yes;">&nbsp; </span><o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">But this post is about the *<b>other</b>* side &ndash; what to do to secure your own passwords, especially those you use online, either in a cloud service or at a provider. While you&rsquo;re not in control of these breaches, there are some things you can do to help protect yourself. Most of these are obvious, but they contain a few little twists that make the process easier.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">Use Complex Passwords<o:p></o:p></span></span></b></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">This is easily stated, and probably one of the most un-heeded piece of advice. There are three main concepts here:<o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;"><span style="font-size:small;">Don&rsquo;t use a dictionary-based word<o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;"><span style="font-size:small;">Use mixed case<o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;"><span style="font-size:small;">Use punctuation, special characters and so on<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;"><i style="mso-bidi-font-style:normal;">So this:</i> password<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;"><i style="mso-bidi-font-style:normal;">Isn&rsquo;t nearly as safe as this:</i> P@ssw03d<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Of course, this only helps if the site that stores your password encrypts it. Gawker does, so theoretically if you had the second password you&rsquo;re in better shape, at least, than the first. Dictionary words are quickly broken, regardless of the encryption, so the more unusual characters you use, and the farther away from the dictionary words you get, the better.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Of course, this doesn&rsquo;t help, not even a little, if the site stores the passwords in clear text, or the key to their encryption is broken. In that case&hellip;<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">Use a Different Password at Every Site<o:p></o:p></span></span></b></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;"><i style="mso-bidi-font-style:normal;">What? I have hundreds of sites! Are you kidding me?</i> Nope &ndash; I&rsquo;m not. If you use the same password at every site, when a site gets attacked, the attacker will store your name and password value for attacks at <i style="mso-bidi-font-style:normal;">other</i> sites. So the only safe thing to do is to use different names or passwords (or both) at each site. Of course, most sites use your e-mail as a username, so you&rsquo;re kind of hosed there. So even though you have hundreds of sites you visit, you need to have at least a different password at each site.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">But it&rsquo;s easier than you think &ndash; if you use an algorithm. <o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">What I&rsquo;m describing is to pick a &ldquo;root&rdquo; password, and then modify that based on the site or purpose. That way, if the site is compromised, you can still use that root password for the other sites.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Let&rsquo;s take that second password:<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">P@ssw03d<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">And now you can append, prepend or intersperse that password with other characters to make it unique to the site. That way you can easily remember the root password, but make it unique to the site. For instance, perhaps you <b style="mso-bidi-font-weight:normal;">read</b> a lot of information on Gawker &ndash; how about these:<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">P@ssw03d<b style="mso-bidi-font-weight:normal;">Read</b><o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;"><b style="mso-bidi-font-weight:normal;">Read</b>P@ssw03d<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">P<b style="mso-bidi-font-weight:normal;">R</b>@<b style="mso-bidi-font-weight:normal;">e</b>s<b style="mso-bidi-font-weight:normal;">a</b>s<b style="mso-bidi-font-weight:normal;">d</b>w03d<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">If you have lots of sites, tracking even this can be difficult, so I recommend you use password software such as Password Safe or some other tool to have a secure database of your passwords at each site. DO NOT store this on the web. DO NOT use an Office document (Microsoft or otherwise) that is &ldquo;encrypted&rdquo; &ndash; the encryption office automation packages use is very trivial, and easily broken. A quick web search for tools to do that should show you how bad a choice this is.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">Change Your Password on a Schedule<o:p></o:p></span></span></b></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">I know. It&rsquo;s a real pain. And it doesn&rsquo;t seem worth it&hellip;until your account gets hacked. A quick note here &ndash; whenever a site gets hacked (and I find out about it) I change the password at that site immediately (or quit doing business with them) and then change the root password on every site, as quickly as I can.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">If you follow the tip above, it&rsquo;s not as hard. Just add another number, year, month, day, something like that into the mix. It&rsquo;s not unlike making a Primary Key in an RDBMS. <o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">P@ssw03dRead<b style="mso-bidi-font-weight:normal;">10242010<o:p></o:p></b></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">Change the site, and then update your password database. I do this about once a month, on the first or last day, during staff meetings. (</span><span style="font-family:Wingdings;mso-ascii-font-family:Calibri;mso-hansi-font-family:Calibri;mso-char-type:symbol;mso-symbol-font-family:Wingdings;"><span style="mso-char-type:symbol;mso-symbol-font-family:Wingdings;">J</span></span><span style="font-family:Calibri;">)<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">If you have other tips, post them here. We can all learn from each other on this.<o:p></o:p></span></span></p><img src="http://sqlblog.com/aggbug.aspx?PostID=31626" width="1" height="1">Cloud ComputingSecurityTipsWindows Azure Security Linkshttp://sqlblog.com/blogs/buck_woody/archive/2010/11/01/windows-azure-security-links.aspxMon, 01 Nov 2010 15:59:48 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:30057BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/30057.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=30057<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Research shows that companies that are considering a &ldquo;cloud&rdquo; platform have various concerns, and that security is at the top of that list. I&rsquo;ve put together a list of the resources I use for explaining our security posture, and the steps that you need to take to be secure in Windows and SQL Azure. I&rsquo;ll try and keep this list current &ndash; if you don&rsquo;t see something that you need, leave me a comment below and I&rsquo;ll research that for you.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Security in any technology should use a multi-layered approach, and that holds true for cloud computing as well. There are things that Microsoft does for security, and things that you need to do to secure your own code and environment. As always, it&rsquo;s best to discuss these items with a technical professional, but these links should provide you some good background to have those discussions. </span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;"></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">This isn&rsquo;t an exhaustive list; there will be other sources you can use for that, but I have it in a format that I think is easy to follow. Most of the links I show here have references to yet other sources as you need them.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">General Information on Cloud Computing Security:<o:p></o:p></span></span></b></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">General Security Whitepaper &ndash; answers most questions: </span><a href="http://blogs.msdn.com/b/usisvde/archive/2010/08/10/security-white-paper-on-windows-azure-answers-many-faq.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/usisvde/archive/2010/08/10/security-white-paper-on-windows-azure-answers-many-faq.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Windows Azure Security Notes from the Patterns and Practices site: </span><a href="http://blogs.msdn.com/b/jmeier/archive/2010/08/03/now-available-azure-security-notes-pdf.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/jmeier/archive/2010/08/03/now-available-azure-security-notes-pdf.aspx</span></a><o:p></o:p></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Great Overview of Azure Security: </span><a href="http://www.windowsecurity.com/articles/Microsoft-Azure-Security-Cloud.html"><span style="font-family:Calibri;font-size:small;">http://www.windowsecurity.com/articles/Microsoft-Azure-Security-Cloud.html</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Azure Security Resources: </span><a href="http://reddevnews.com/articles/2010/08/19/microsoft-releases-windows-azure-security-resources.aspx"><span style="font-family:Calibri;font-size:small;">http://reddevnews.com/articles/2010/08/19/microsoft-releases-windows-azure-security-resources.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Cloud Computing Security Considerations: </span><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=68fedf9c-1c27-4642-aa5b-0a34472303ea&amp;utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+MicrosoftDownloadCenter+%28Microsoft+Download+Center"><span style="font-family:arial,helvetica,sans-serif;"><span style="font-size:x-small;">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=68fedf9c-1c27-4642-aa5b-0a34472303ea&amp;utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+MicrosoftDownloadCenter+%28Microsoft+Download+Center</span></span></a><span style="font-size:small;"><span style="font-family:arial,helvetica,sans-serif;"><span style="font-size:x-small;"> <o:p></o:p></span></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Security in Cloud Computing &ndash; a Microsoft Perspective: </span><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7c8507e8-50ca-4693-aa5a-34b7c24f4579&amp;utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+MicrosoftDownloadCenter+%28Microsoft+Download+Center"><span style="font-family:arial,helvetica,sans-serif;"><span style="font-size:x-small;">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7c8507e8-50ca-4693-aa5a-34b7c24f4579&amp;utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+MicrosoftDownloadCenter+%28Microsoft+Download+Center</span></span></a><span style="font-size:small;"><span style="font-family:arial,helvetica,sans-serif;"><span style="font-size:x-small;"> <o:p></o:p></span></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">Physical Security for Microsoft&rsquo;s Online Computing:<o:p></o:p></span></span></b></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l2 level1 lfo3;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">The Global Foundation Services group at Microsoft handles our physical security. It&rsquo;s quite robust, and meets </span><a href="http://www.27000.org/iso-27001.htm"><span style="font-family:Calibri;font-size:small;">ISO 27001</span></a><span style="font-family:Calibri;font-size:small;"> and </span><a href="http://sas70.com/sas70_overview.html"><span style="font-family:Calibri;font-size:small;">SAS-70</span></a><span style="font-family:Calibri;font-size:small;"> requirements. More here: </span><a href="http://www.globalfoundationservices.com/security/index.html"><span style="font-family:Calibri;font-size:small;">http://www.globalfoundationservices.com/security/index.html</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l2 level1 lfo3;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Microsoft&rsquo;s Security Response Center: </span><a href="http://www.microsoft.com/security/msrc/"><span style="font-family:Calibri;font-size:small;">http://www.microsoft.com/security/msrc/</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">Software Security for Microsoft&rsquo;s Online Computing:<o:p></o:p></span></span></b></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo2;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Windows Azure is developed using the Trustworthy Computing Initiative - you should follow this as well: </span><a href="http://www.microsoft.com/about/twc/en/us/default.aspx"><span style="font-family:Calibri;font-size:small;">http://www.microsoft.com/about/twc/en/us/default.aspx</span></a><span style="font-family:Calibri;font-size:small;"> and </span><a href="http://msdn.microsoft.com/en-us/library/ms995349.aspx"><span style="font-family:Calibri;font-size:small;">http://msdn.microsoft.com/en-us/library/ms995349.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo2;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Identity and Access in the Cloud: </span><a href="http://blogs.msdn.com/b/technology_titbits_by_rajesh_makhija/archive/2010/10/29/identity-and-access-in-the-cloud.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/technology_titbits_by_rajesh_makhija/archive/2010/10/29/identity-and-access-in-the-cloud.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="font-family:Calibri;"><span style="font-size:small;">Security Steps you should take:<o:p></o:p></span></span></b></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Securing your cloud architecture, step-by-step: </span><a href="http://technet.microsoft.com/en-us/magazine/gg296364.aspx"><span style="font-family:Calibri;font-size:small;">http://technet.microsoft.com/en-us/magazine/gg296364.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Security Guidelines for Windows Azure: </span><a href="http://redmondmag.com/articles/2010/06/15/microsoft-issues-security-guidelines-for-windows-azure.aspx"><span style="font-family:Calibri;font-size:small;">http://redmondmag.com/articles/2010/06/15/microsoft-issues-security-guidelines-for-windows-azure.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Best Practices for Windows Azure Security: </span><a href="http://blogs.msdn.com/b/vbertocci/archive/2010/06/14/security-best-practices-for-developing-windows-azure-applications.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/vbertocci/archive/2010/06/14/security-best-practices-for-developing-windows-azure-applications.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Active Directory and Windows Azure: </span><a href="http://blogs.msdn.com/b/plankytronixx/archive/2010/10/22/projecting-your-active-directory-identity-to-the-azure-cloud.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/plankytronixx/archive/2010/10/22/projecting-your-active-directory-identity-to-the-azure-cloud.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Understanding Encryption (great overview and tutorial): </span><a href="http://blogs.msdn.com/b/plankytronixx/archive/2010/10/23/crypto-primer-understanding-encryption-public-private-key-signatures-and-certificates.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/plankytronixx/archive/2010/10/23/crypto-primer-understanding-encryption-public-private-key-signatures-and-certificates.aspx</span></a><o:p></o:p></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Securing your Connection Strings: </span><a href="http://blogs.msdn.com/b/sqlazure/archive/2010/09/07/10058942.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/sqlazure/archive/2010/09/07/10058942.aspx</span></a><span style="font-family:Calibri;"><span style="font-size:small;"> <o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l1 level1 lfo1;"><span style="font-family:Symbol;mso-fareast-font-family:Symbol;mso-bidi-font-family:Symbol;"><span style="mso-list:Ignore;"><span style="font-size:small;">&middot;</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;font-size:small;">Getting started with Windows Identity Foundation (WIF) quickly: </span><a href="http://blogs.msdn.com/b/alikl/archive/2010/10/26/windows-identity-foundation-wif-fast-track.aspx"><span style="font-family:Calibri;font-size:small;">http://blogs.msdn.com/b/alikl/archive/2010/10/26/windows-identity-foundation-wif-fast-track.aspx</span></a><span style="font-size:small;"><span style="font-family:Calibri;"> <o:p></o:p></span></span></p><img src="http://sqlblog.com/aggbug.aspx?PostID=30057" width="1" height="1">AzureCloudSecuritySQL AzureSchemas as Security Boundarieshttp://sqlblog.com/blogs/buck_woody/archive/2010/08/03/schemas-as-security-boundaries.aspxTue, 03 Aug 2010 13:00:25 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:27563BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/27563.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=27563<p>There was a question yesterday on Twitter (hashtag #sqlhelp) wondering how to let developers create stored procedures and then grant the rights to those procedures to other people. I believe that question got answered, but it also brought up the subject of Schemas, which I've blogged about before. </p>
<p>Schemas can act both as a container and a security boundary. That means you can combine a role and schema in SQL Server to create an "area" or bucket of things you want the developer to have full control over, without having to make them a full database owner. I would show you that process here, complete with an example and so on - but happily theSQL Server best practices team beat me to it. Check this link, and move to the middle of the page - where it starts with "Using Schemas in SQL Server": <a href="http://msdn.microsoft.com/en-us/library/dd283095(SQL.100).aspx">http://msdn.microsoft.com/en-us/library/dd283095(SQL.100).aspx</a>&nbsp;</p>
<p>Yet another great reason to learn and use schemas...</p><img src="http://sqlblog.com/aggbug.aspx?PostID=27563" width="1" height="1">SchemasSecuritySQL ServerDon’t mess with the system databases in SQL Server, or Error: 916http://sqlblog.com/blogs/buck_woody/archive/2010/08/02/don-t-mess-with-the-system-databases-in-sql-server-or-error-916.aspxMon, 02 Aug 2010 13:41:12 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:27505BuckWoody7http://sqlblog.com/blogs/buck_woody/comments/27505.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=27505<p class="MsoNormal" style="margin:0in 0in 0pt;"><i style="mso-bidi-font-style:normal;"><span style="color:red;"><span style="font-size:small;"><span style="font-family:Calibri;">Note: If you&rsquo;re reading this more than a few months away from July of 2010, do more research. Never trust an old blog as gospel on anything, including my entries. Always refer to Books Online for the authoritative answer, and if it&rsquo;s wrong, file a bug against it using the &ldquo;Feedback&rdquo; Button. <o:p></o:p></span></span></span></i></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">It kinds of goes without saying (so of course I&rsquo;m saying it) that unless you have a *<b>really</b>* compelling reason to change anything in the system databases you shouldn&rsquo;t. And by &ldquo;system databases&rdquo; what I mean are the big four:<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="mso-fareast-font-family:Calibri;mso-bidi-font-family:Calibri;"><span style="mso-list:Ignore;"><span style="font-family:Calibri;font-size:small;">1.</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-size:small;"><span style="font-family:Calibri;">master<o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="mso-fareast-font-family:Calibri;mso-bidi-font-family:Calibri;"><span style="mso-list:Ignore;"><span style="font-family:Calibri;font-size:small;">2.</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-size:small;"><span style="font-family:Calibri;">model<o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="mso-fareast-font-family:Calibri;mso-bidi-font-family:Calibri;"><span style="mso-list:Ignore;"><span style="font-family:Calibri;font-size:small;">3.</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-size:small;"><span style="font-family:Calibri;">msdb<o:p></o:p></span></span></p>
<p class="MsoListParagraph" style="text-indent:-0.25in;margin:0in 0in 0pt 0.5in;mso-list:l0 level1 lfo1;"><span style="mso-fareast-font-family:Calibri;mso-bidi-font-family:Calibri;"><span style="mso-list:Ignore;"><span style="font-family:Calibri;font-size:small;">4.</span><span style="font:7pt 'Times New Roman';">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><span style="font-family:Calibri;"><span style="font-size:small;">tempdb<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">In some cases however - specifically in the security area - we (Microsoft) have been less than clear on the system databases. I want to address one particular issue that&rsquo;s been going around in discussions on the web, so I want to make sure I clear this up carefully.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p align="center" class="MsoNormal" style="text-align:center;margin:0in 0in 0pt;"><b style="mso-bidi-font-weight:normal;"><span style="text-decoration:underline;"><span style="font-family:Calibri;"><span style="font-size:small;">Statement: Don&rsquo;t remove the &ldquo;guest&rdquo; account from the <i style="mso-bidi-font-style:normal;">msdb</i> system database.<o:p></o:p></span></span></span></b></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">Hopefully that&rsquo;s clear. Just don&rsquo;t remove it. It&rsquo;s not a bug that it's in there.&nbsp;You need to keep the guest account in <i style="mso-bidi-font-style:normal;">msdb</i> for LOTS of stuff to work, from Policy Based Management (PBM) all the way to SQL Server Management Studio. If you do remove it, you&rsquo;re apt to get this message&nbsp;(but only if you&rsquo;re not in the <em>sysadmin </em>group):<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;padding-left:30px;"><span style="font-size:small;"><span style="color:#008000;">Failed to retrieve data for this request. (Microsoft.SqlServer.Manager.Sdk.Sfc)<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;padding-left:30px;"><span style="font-size:small;"><span style="color:#008000;">Additional Information:<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;padding-left:30px;"><span style="font-size:small;"><span style="color:#008000;">An exception occurred while executing a Transact-SQL statement or batch.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;padding-left:30px;"><span style="font-size:small;"><span style="color:#008000;">(Microsoft.SqlServer.ConnectionInfo)<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;padding-left:30px;"><span style="font-size:small;"><span style="color:#008000;">The server principal &ldquo;Buck&rdquo; is not able to access the database &ldquo;msdb&rdquo; under the current security context. (Microsoft SQL Server, Error: 916)<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;"><span style="font-size:small;">I know, this is a very rare thing, and if you change something and then things quit working, you&rsquo;ll probably put 2 + 2 together to know what happened. But just in case an admin removes it and you can&rsquo;t access your databases through SSMS any more, well, there you go.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-family:Calibri;font-size:small;">We DO have documentation on this: </span><a target="_blank" href="http://msdn.microsoft.com/en-us/library/ee342155.aspx"><span style="font-family:Times New Roman;color:#0000ff;font-size:small;">http://msdn.microsoft.com/en-us/library/ee342155.aspx</span></a><span style="font-size:small;"><span style="font-family:Calibri;"><span style="mso-spacerun:yes;">&nbsp; </span>and we&rsquo;ll be updating the security best practices whitepapers we have to make this very clear. But since some guidleines tend to sound like you should remove guest from EVERY database, I wanted to make sure you know what to do in the meantime.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">My friend Cliff Dibble, a Principal Program Manager on the same team at SQL Server I worked at has provided us a script you can use to see if you have the issue:<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">/* Find the issue of 916 if result set is empty, you have the issue */<br />USE msdb;<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">SELECT prins.name AS grantee_name, perms.*<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">FROM<span style="mso-spacerun:yes;">&nbsp;&nbsp; </span>sys.database_permissions AS perms<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">JOIN<span style="mso-spacerun:yes;">&nbsp;&nbsp; </span>sys.database_principals AS prins<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">ON<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span>perms.grantee_principal_id = prins.principal_id<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">WHERE<span style="mso-spacerun:yes;">&nbsp; </span>prins.name = 'guest' AND perms.permission_name = 'CONNECT';<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">GO<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">/* Fix issue */<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">USE msdb;<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">GRANT connect TO guest;<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">GO<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><span style="font-size:small;"><span style="font-family:Calibri;">So there you have it. Look for more clear guidance in our security tools forthcoming.<o:p></o:p></span></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt;"><o:p><span style="font-family:Calibri;font-size:small;">&nbsp;</span></o:p></p><img src="http://sqlblog.com/aggbug.aspx?PostID=27505" width="1" height="1">SecuritySQL ServerThe TechNet Wiki and Updated Security Checklistshttp://sqlblog.com/blogs/buck_woody/archive/2010/07/27/the-technet-wiki-and-updated-security-checklists.aspxTue, 27 Jul 2010 13:18:20 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:27334BuckWoody1http://sqlblog.com/blogs/buck_woody/comments/27334.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=27334<p>You're probably familiar with a Wiki - a document set that anyone can edit. Did you know TechNet (Microsoft's source for technical professionals) has one? And did you know there are lots of folks keeping it up to date? Well, Rick Byham, one of my friends over in the SQL Server group has posted a bunch of security checklists - and you know how much I love checklists! You can go to the Wiki here: <a href="http://social.technet.microsoft.com/wiki/">http://social.technet.microsoft.com/wiki/</a>&nbsp;and search for "Checklists", but here's what he's posted to get you started:</p>
<p class="MsoNormal" style="margin:0in 0in 0pt 0.5in;"><a href="http://social.technet.microsoft.com/wiki/contents/articles/database-engine-security-checklist-encrypting-sensitive-data.aspx"><span style="mso-ansi-language:EN;"><span style="font-family:Times New Roman;color:#0000ff;font-size:small;">Database Engine Security Checklist: Encrypting Sensitive Data</span></span></a><span style="font-family:'Times New Roman','serif';font-size:12pt;mso-ansi-language:EN;"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt 0.5in;"><a href="http://social.technet.microsoft.com/wiki/contents/articles/database-engine-security-checklist-enhancing-the-security-of-database-engine-connections.aspx"><span style="mso-ansi-language:EN;"><span style="font-family:Times New Roman;color:#0000ff;font-size:small;">Database Engine Security Checklist: Enhancing the Security of Database Engine Connections</span></span></a><span style="mso-ansi-language:EN;"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt 0.5in;"><a href="http://social.technet.microsoft.com/wiki/contents/articles/database-engine-security-checklist-limiting-access-to-data.aspx"><span style="mso-ansi-language:EN;"><span style="font-family:Times New Roman;color:#0000ff;font-size:small;">Database Engine Security Checklist: Limiting Access to Data</span></span></a><span style="mso-ansi-language:EN;"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin:0in 0in 0pt 0.5in;"><a href="http://social.technet.microsoft.com/wiki/contents/articles/database-engine-security-checklist-database-engine-security-configuration.aspx"><span style="mso-ansi-language:EN;"><span style="font-family:Times New Roman;color:#0000ff;font-size:small;">Database Engine Security Checklist: Database Engine Security Configuration</span></span></a><span style="mso-ansi-language:EN;"><o:p></o:p></span></p>
<p>
<p>Thanks, Rick!</p>
</p><img src="http://sqlblog.com/aggbug.aspx?PostID=27334" width="1" height="1">ChecklistsSecuritySQL ServerCross-Pollinationhttp://sqlblog.com/blogs/buck_woody/archive/2010/07/13/cross-pollination.aspxTue, 13 Jul 2010 12:10:10 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:26985BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/26985.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=26985<p>I was reading <a href="http://blogs.msdn.com/b/jmeier/archive/2010/07/08/cloud-security-threats-and-countermeasures-at-a-glance.aspx" target="_blank">this post on J.D. Meier's Blog, which deals with the “cloud” (I really dislike that term)</a>. You might wonder what that has to do with SQL Server, since it isn’t specifically about SQL Azure. I’ll come back to that in a moment.</p> <p>I play a little music now and then, on the keyboards and with a guitar as well as the mandolin and banjo. I’m not very good, although I do play in public each week. I try to get better all the time, but sometimes I hit a “wall” – not in the mechanics of playing like finger-positioning or scales or things like that, but being able to improvise new lines and riffs. So a friend gave me some interesting advice. He said: “Go learn to draw.”</p> <p>Now, if I’m not an awesome musician, I’m an even less-awesome artist. I can certainly appreciate art, and I can put nice things together on a screen or PowerPoint demo, I’ve never really been able to draw 3-D art like the masters do. But I took his advice, set up a pen-tablet for my PC, and grabbed a few books on learning to draw. I’ve watched painting shows on PBS, talked to artists, and had folks show me how to draw better. </p> <p>And I can play better now. Isn’t that strange? No, I didn’t draw anything that has to do with music – but putting my mind towards another creative effort allowed me to get better at the first one. There’s enough thought-processes in one that helped me in the other.</p> <p>So now let’s talk about that article I mentioned a moment ago. No, it doesn’t deal with SQL Server, but I really like the approach he takes in his blog post. He lays out everything very clearly, deals with the topic that people ask about a great deal, and I like the set-up for the table that follows his topic. It’s something I’ll incorporate into my security plans for databases going forward.</p> <p>So what does this mean to you? Study some new development language. Read about chip technology. Go back and practice some math. Find things that are tangential to database technology and the business of what you do, and read, do, practice and try those things. You’ll find it helps “round you out” as a data professional.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=26985" width="1" height="1">CareerLearningSecuritySQL ServerMore 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:25343BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/25343.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=25343<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><img src="http://sqlblog.com/aggbug.aspx?PostID=25343" width="1" height="1">AdministrationBest PracticesDBASecuritySecurity Goes Undergroundhttp://sqlblog.com/blogs/buck_woody/archive/2010/04/22/security-goes-underground.aspxThu, 22 Apr 2010 13:06:16 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:24492BuckWoody0http://sqlblog.com/blogs/buck_woody/comments/24492.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=24492<p>You might not have heard of as many data breaches recently as in the past. As you’re probably aware, I call them out here as often as I can, especially the big ones in government and medical institutions, because I believe those can have lasting implications on a person’s life.</p> <p>I think that my data is personal – and I’ve seen the impact of someone having their identity stolen. It’s a brutal experience that I wouldn’t wish on anyone. So with all of that it stands to reason that I hold the data professionals to the highest standards on security. I think your first role is to ensure the data you have, number one because it can be so harmful, and number two because it isn’t yours. It belongs to the person that has that data.</p> <p>You might think I’m happy about that downturn in reported data losses. Well, I was, until I learned that companies have realized they suffer a lowering of their stock when they report it, but not when they don’t. So, since we all do what we are measured on, they don’t. So now, not only are they not protecting your information, they are hiding the fact that they are losing it.</p> <p>So take this as a personal challenge. Make sure you have a security audit on your data, and treat any breach like a personal failure. We’re the gatekeepers, so let’s keep the gates.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=24492" width="1" height="1">SecuritySQL ServerBackup 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:24408BuckWoody1http://sqlblog.com/blogs/buck_woody/comments/24408.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=24408<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><img src="http://sqlblog.com/aggbug.aspx?PostID=24408" width="1" height="1">AdministrationBest PracticesDBADisaster RecoveryMaintenanceMaintenance PlansSecuritySQL ServerTipsHave 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:22679BuckWoody4http://sqlblog.com/blogs/buck_woody/comments/22679.aspxhttp://sqlblog.com/blogs/buck_woody/commentrss.aspx?PostID=22679<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><img src="http://sqlblog.com/aggbug.aspx?PostID=22679" width="1" height="1">AdministrationBest PracticesDBADisaster RecoveryMaintenanceMaintenance PlansSecuritySQL ServerTips