Knowledge Center

  • SQL Server 2008: Database Auditing Standards and Best Practices

    by Steven Allen | May 13, 2015
    This will be the first in a series of blog posts in the next several months designed to touch on many of the topics covered in QuickStart’s SQL Server 2008 training courses; mores specifically database auditing with guidance and discussion for SQL Server 2008 and SQL Server 2008 R2. If the next version of SQL Server (code named ‘Denali’) adds or changes this, I will cover that towards the end of the series. In this first blog, we’ll look regulatory requirements, general organizational security practices, and auditing best practices for databases irrespective to the DBMS chosen. The second blog will continue looking at best practices, specifically what audit event data needs to be in the log and protecting audit systems and data. In the third blog, I’ll discuss the available auditing methods in SQL Server 2008/R2 and the advantages and disadvantages of each one. In the fourth and fifth blogs we’ll do a deeper dive into two specific auditing mechanisms, SQL Server Audit (built-in fine grain auditing introduced in SQL Server 2008), SQL Server Event Notifications (introduced in SQL Server 2005).
    Full story
  • Sequencing Identity Numbers in SQL Server 2012

    by Jeff Rathjen | May 13, 2015
    Identity columns are nothing new in T-SQL. They are great when you want a unique number to be automatically assigned every time you insert a new record. The trouble is that while they are unique within the table, they are probably the same between tables. Every table with an identity column has a 1 for the first record, a 2 for the second, etc. Of course you can set the seed and increment for the identity column to something other than (1,1), but you still have to manually track which table has which seed. Trying to track that is destined for failure. What we really need is a way to have automatically assigned numbers for new records, but ones which are guaranteed to be unique across any table that uses them. Of course, you know I wouldn’t be writing this if there weren’t a solution. In SQL Server 2012, Microsoft introduced something called the SEQUENCE object that does this very thing.
    Full story
  • Top 5 Commands in PowerShell

    by Saskia Schott | May 13, 2015
    Which commands are you always going to need to use, and will use with every new command or module as you write PowerShell? 1. Get-Help. You use this to read the help file for a cmdlet: Get-help . So you see a script that uses Test-connection, and you want to see what you can do with it: get-help test-connection. If you want to see it in a separate window, in PowerShell v3 and higher: Get-help Test-connection –showwindow. This opens full help in a separate window. You can limit what you see to just examples by changing the settings (upper right hand corner of the window). Get-help has other parameters: -examples, -parameters, -full or –detailed, which result in help being shown to you in your PowerShell session. Online help (use the –online parameter) will open a browser window to the proper MSDN help page.
    Full story
  • Microsoft Report Builder 3.0

    by Saskia Schott | May 13, 2015
    With the release of SQL 08 R2, Microsoft is making Report Builder 3.0 available. It has been a long road from the original Report Builder 1.0. It was a little clunky, not very intuitive, and was dependent on Report Models. Report Models are an abstraction of the data, and are generally designed by the BI Developer to expose data to the Business User for report creation.
    Full story
  • PowerShell Credentials and SecureStrings, Part II

    by Steven Allen | May 13, 2015
    In my first blog post on using secure passwords in PowerShell scripts, I discussed methods in PowerShell to keep passwords and other secure strings secure in memory and protect against memory dump attacks and the like. I showed how to use those secure strings to run commands either an alternate username and password if the commands ask for those or by building and using PSCredential objects for those commands that expect a full credential objects. At the end of the blog I showed how to convert that secure string to a regular encrypted string using the Windows Data Protection API (DPAPI). While highly secure, that method of encrypting the string is limited to that one user account on that one computer by the way it uses the Windows DPAPI.
    Full story
  • Microsoft Operations Framework (MOF) v4

    by Saskia Schott | May 13, 2015
    Wait, what’s a MOF? Microsoft Operations Framework? Why would I need that? In the 1980’s there was a movement to create standards for managing IT within the British government. The result was the Information Technology Infrastructure Library (ITIL) was initially published as a series of books between 1989 and 1996. It documented IT best practices without regard to platform, nationality, industry or size of organization. The sponsoring organization, the Central Computer and Telecommunications Industry, (CCTA) enlisted experts from various telecom and computer companies to write and edit the guidance. Microsoft was one of those companies.
    Full story
  • SharePoint Designer Governance

    by Steven Allen | May 13, 2015
    I hear these and many similar comments and questions all the time in my SharePoint Site Administrator/Power User classes: “Oh, I can’t do that because my company won’t let us use SharePoint Designer.” “I’d like to create a workflow, but no one is allowed to use SharePoint Designer.” “How do I get my company to let me use SharePoint Designer?”
    Full story
  • 3 ways to Monitor with PowerShell

    by Saskia Schott | May 13, 2015
    A student in a recent class asked for a script that could take every command a user typed into PowerShell and send it to a csv file for monitoring purposes. That prompted me to think about the choices you have with PowerShell. He didn’t want to capture history after the fact, with a ‘get-history’ command, since the user could easily have run ‘clear-history’ resulting in the removal of the commands they had run to that point. That made me think about what his choices were. His chosen alternative was to send events to the PowerShell event log, however, when monitoring anything, you could also decide to send data a SQL database. My thanks to David W for his question, and his eventual script, which you can see below.
    Full story
  • Auditing Tools in SQL Server 2008 and SQL Server 2008 R2

    by Jeff Rathjen | May 13, 2015
    This is the third article in a series discussing database auditing. The first article discussed the need to audit from various governmental mandates and best practices of auditing just the data you need to meet requirements. The second article discussed the auditing log data best practices, both what event data to include in your audit logs and the need to protect your audit log systems and data from tampering. This article will look at the various tools that we can use for auditing and the pros and cons of various tools that SQL Server 2008 provides to us administrators and developers to enable auditing compliance.
    Full story
  • PowerShell Credentials and SecureStrings, Part III

    by Steven Allen | May 13, 2015
    In my first blog post on using secure passwords in PowerShell scripts, I explained how the .NET Framework uses Strings and a new object type, SecureStrings. SecureStrings have several features that protect against attacks to read string values from memory, not the least of which is that the string is encrypted from the start in memory. I then introduced the Get-PSCredentialcmdlet that will prompt for a username and password and store that password in a SecureString property, with the property incidentally named “Password”. An alternative discussed there, when only a SecureString is needed and not a full credential, was the Read-Host cmdlet with the –AsSecureString parameter.
    Full story
  • PowerShell Credentials and SecureStrings, Part I

    by Steven Allen | May 13, 2015
    I frequently get asked questions in my PowerShell classes about providing credentials to commands and scripts, including storing passwords in scripts. In this blog post I’ll discuss how the .NET Framework handles strings and why that behavior is undesirable for working with passwords and other secure strings of data. I will also discuss methods of providing passwords, credentials, and other secure strings into our commands and scripts interactively. At the end I will introduce a method of securely saving credentials so that a script can be executed without prompting for passwords, although the script must be executed by the user who entered the credentials. In a follow up post, I’ll discuss options when you need multiple people to execute scripts with saved credentials.
    Full story
  • Top 5 PowerShell Scripting Sources

    by Saskia Schott | Mar 25, 2015
    In a recent class, I pointed out to students some of my favorite scripting sources, and found that some students had found one or two, but not all my favorites. So, since PowerShell is all about finding the right script, here are my top five websites for PowerShell scripts. And, by the way, the right script might be the one that does exactly what you need, or it could be the one that does something very close to what you need, and you can modify.
    Full story