SQLCMD Output Demangler - Version 1.0

Welcome to my first post of February 2012.  Today's topic is the atrocious output sent forth by SQLCMD.  For those accidental DBAs unfamiliar with SQLCMD, it's essentially the command-line interface with SQL Server, similar to using a New Query window in SSMS.  It is the successor to OSQL, which was used with SQL Server 7 and 2000.


As seasoned DBAs will know, running a SQL query in SQLCMD and getting human-readable output can be very difficult.  Consider the following example:


GETINFO.SQL
===========
SELECT ed.name AS 'Connection_Type', ec.encrypt_option AS 'Encrypted', 
ec.auth_scheme AS 'Authentication_Method',
ec.client_net_address AS 'IP_Address',
(ec.net_packet_size*ec.num_reads) AS 'Bytes_Read',
(ec.net_packet_size*ec.num_writes) AS 'Bytes_Written',
(DATEDIFF(ss,ec.connect_time,SYSDATETIMEOFFSET())) AS 'Alive'


FROM sys.dm_exec_connections ec INNER JOIN sys.endpoints ed
ON ec.endpoint_id=ed.endpoint_id


ORDER BY Alive DESC


--(Note that SYSDATETIMEOFFSET() won't work on some versions, use GETDATE() instead).


Which renders as this:

While many developers have written parsers, particularly when accessing SQL Server as a service from an application layer (i.e. using ADODB or SQL Native Client connections), where does this leave your garden-variety DBA restricted for whatever reason to SQLCMD?  What about retrieving output in CSV format for data analysis, or retrieving information on-the-fly that is readable and recognisable?


I faced this situation not long ago and resolved to do something about it.  Although my solution is far from elegant - it involves procedural, not OOP, based programming - it works and takes a target server and query file as inputs, outputting a formatted CSV and the same information in a HTML file opened in your local browser.  Also, it's based on technology available for any Windows XP + based system - the Windows Scripting Host, compiling VBScript - and the humble Windows batch file.  There's a bit of string bludgeoning in there too.


OK, let's start at the beginning.  Create a text file named 'go.bat' and put it in the directory of your choice (preferably an empty one):


GO.BAT
======
@echo off
set counter=0
cls
goto errorCatcher


:errorCatcher
IF "%1"=="" GOTO :wrong
IF "%2"=="" GOTO :wrong
set counter=0
goto main


:main
ping 127.0.0.1 > NUL
SQLCMD -S%1 -E -i%2 > results.tmp
CSCRIPT PARSER.VBS 
TESTHTML.BAT


:wrong
echo Usage is go [SQL Server hostname\instance] [SQL script]
echo .
echo E.g. 'go localhost doSomething.sql 30'
echo Then the script specified is executed, once only, in the main window.
echo Output goes to OUTPUT.CSV
echo .


:eof


So what does this code do?


The constructor, for want of a better word, turns terminal output off and clears the screen. Then the :errorCatcher subroutine is invoked using the oldest of all old programming commands - GOTO.  The :errorCatcher routine checks the input parameters.  %1 is the target - if blank, catch the error by diverting to :wrong.  %2 is the input file name.  Counter is then reinitialised to 0.  Next, :main introduces a brief wait (again for testing - REM this out if you like) before invoking SQLCMD and passing it the target server and input SQL file.  Output is to the file results.tmp.  The next step is to start the parser by passing the .vbs file to the WSH.


:wrong is simply a routine to catch an error and exit the script.


Assuming you run this with valid parameters, and all goes well, PARSER begins.  

 PARSER.VBS
==========
Set fs=CreateObject("Scripting.FileSystemObject")
Set inFile=fs.OpenTextFile("results.tmp")
Set outFile=fs.CreateTextFile("parsedResults.csv")
Dim nowChar,nameContainer,doneFlag,counter,prevChar,lcFlag
Dim headerArray()
nowChar=" "
prevChar=" "
counter=0
doneFlag=0
lcFlag=0


Call processHeaders
inFile.SkipLine
WScript.StdOut.WriteLine(" ")
Call processRows




Sub processHeaders
Do While inFile.AtEndOfLine <> True  
 nowChar=inFile.Read(1)
 If nowChar<>" " Then
nameContainer=(nameContainer & nowChar)
doneFlag=0
 ElseIf nowChar=" " And doneFlag=0 Then
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
 outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Or inFile.AtEndOfLine = True Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
nameContainer=""
doneFlag=1
 End If
Loop
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
 outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
inFile.SkipLine
outFile.WriteLine("")
lcFlag=0
End Sub


Sub processRows
Do While inFile.AtEndOfStream <> True
nowChar=" "
counter=0
doneFlag=0
nameContainer=""
Do While inFile.AtEndOfLine <> True
 prevChar=nowChar
 nowChar=inFile.Read(1)
 If nowChar="(" Then
   Exit Do
 End If    
 If nowChar<>" " Then
   nameContainer=(nameContainer & nowChar)
   doneFlag=0
 ElseIf nowChar=" " And prevChar<>" " Then
   nameContainer=(nameContainer & " ")
 ElseIf nowChar=" " And prevChar=" " And doneFlag=0 Then
        nameContainer=LTrim(RTrim(nameContainer))
   If lcFlag=1 Then
 outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
nameContainer=""
doneFlag=1
 End If  
        Loop
    If nowChar="(" Then
      Exit Do
    End If
    nameContainer=LTrim(RTrim(nameContainer))
If lcFlag=1 Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
    inFile.SkipLine
outFile.WriteLine(" ")
lcFlag=0
Loop
End Sub


So this is a bit more complicated.  Section by section:

 Set fs=CreateObject("Scripting.FileSystemObject")
Set inFile=fs.OpenTextFile("results.tmp")
Set outFile=fs.CreateTextFile("parsedResults.csv")
Dim nowChar,nameContainer,doneFlag,counter,prevChar,lcFlag
Dim headerArray()
nowChar=" "
prevChar=" "
counter=0
doneFlag=0
lcFlag=0


This section simply constructs the variables we'll be using.  Input/output files are handled using the Textstream object.  headerArray() is a dynamic array NOT of type Array() but actually of WScript.Collection.  


Call processHeaders
inFile.SkipLine
WScript.StdOut.WriteLine(" ")
Call processRows


This is fairly self-explanatory.  First the sub-routine processHeaders is called, a bit of output formatting, then the second sub-routine processRows is called.  Important difference between subs and functions - subs don't take parameters, functions do.  But global variables (i.e. those defined outside the subs/functions) persist regardless.


Sub processHeaders
Do While inFile.AtEndOfLine <> True  
 nowChar=inFile.Read(1)
 If nowChar<>" " Then
nameContainer=(nameContainer & nowChar)
doneFlag=0
 ElseIf nowChar=" " And doneFlag=0 Then
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
 outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Or inFile.AtEndOfLine = True Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
nameContainer=""
doneFlag=1
 End If
Loop
nameContainer=LTrim(RTrim(nameContainer))
counter=counter+1
Redim Preserve headerArray(counter)
headerArray(counter)=nameContainer
If lcFlag=1 And inFile.AtEndOfLine <> True Then
 outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
inFile.SkipLine
outFile.WriteLine("")
lcFlag=0
End Sub


OK - in English.  While the file pointer is not at the end of the line, read into a variable nowChar one character from the input file (results.tmp).  If the character is not a space, append the character to a string.  If it is a space and the word is not marked as complete, take the string, trim it and add it to the dynamic array.  Then output to file a comma, then the string.  Then reset the string.  There's an IF loop in there to detect if the word is the last in the file, if so don't put the comma in (so the row delimiter remains as /n, not ,).


Once the end of the line is reached, do the same with the last string in the variable lineContainer.  Finally, reset the variables for the next sub.


Sub processRows
Do While inFile.AtEndOfStream <> True
nowChar=" "
counter=0
doneFlag=0
nameContainer=""
Do While inFile.AtEndOfLine <> True
 prevChar=nowChar
 nowChar=inFile.Read(1)
 If nowChar="(" Then
   Exit Do
 End If    
 If nowChar<>" " Then
   nameContainer=(nameContainer & nowChar)
   doneFlag=0
 ElseIf nowChar=" " And prevChar<>" " Then
   nameContainer=(nameContainer & " ")
 ElseIf nowChar=" " And prevChar=" " And doneFlag=0 Then
        nameContainer=LTrim(RTrim(nameContainer))
   If lcFlag=1 Then
 outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
nameContainer=""
doneFlag=1
 End If  
        Loop
    If nowChar="(" Then
      Exit Do
    End If
    nameContainer=LTrim(RTrim(nameContainer))
If lcFlag=1 Then
outFile.Write("," & nameContainer)
ElseIf lcFlag=0 Then
 outFile.Write(nameContainer)
 lcFlag=1
End If
    inFile.SkipLine
outFile.WriteLine(" ")
lcFlag=0
Loop
End Sub


Very similar operation here, except this has to cope with multiple rows.  By the way, the SkipLine operation between the subs is to skip the -------- line output by default in most SQLCMD outputs.  Operation is the same, except the whole script is encapsulated in a Do While loop that checks to see if the file pointer is at the end of the file yet.  There's a couple of minor differences too, by default the end of SQLCMD output has (x rows affected).  There's another quick check in the code to see if ( is found, if so the line is skipped (by exiting the nested DO WHILE to the outer loop).


The output of this script, PARSER.VBS, outputs a valid .csv file with the headers comma-separated on the top row, values below.  I.e:


Connection_Type,Encrypted,Authentication_Method,IP_Address,Bytes_Read,Bytes_Written,Alive
TSQL Local Machine,FALSE,NTLM,<local machine>,24576,24576,268 
TSQL Local Machine,FALSE,NTLM,<local machine>,28672,24576,0 


As you'll recall, in go.bat, TESTHTML.BAT is also called.  You can bin this if you like, but it's a handy little scriptlet for writing the contents of the CSV out to a HTML table.  You can replace this with a bit of custom PHP if you like:


TESTHTML.BAT
============
@echo off
ERASE OUTPUT.HTML 2>NUL
SET PATH=%PATH%;"C:\DOCUMENTS AND SETTINGS\DEL\LOCAL SETTINGS\APPLICATION DATA\GOOGLE\CHROME\APPLICATION"
cscript html_writer.vbs 1>NUL
chrome.exe file://<path>\output.html


You can see here I've adapted it to open in Chrome.  Pick your favourite browser and change this accordingly.  This invokes html_writer.vbs which directs standard output to NUL - this is because it's effectively a blind sub, I don't want any terminal output - only to file.  


The erase command is just a bit of cleaning up.




HTML_WRITER.VBS
===============


Set fs=CreateObject("Scripting.FileSystemObject")
Set inFile=fs.OpenTextFile("parsedResults.csv")
Set outFile=fs.CreateTextFile("output.html")


Dim delim,charContainer,lineContainer,counter,wordArray()
delim=","
counter=0


Call writeHTMLHeader


Do While inFile.AtEndOfStream <> True
Do While inFile.AtEndOfLine <> True
 charContainer=CStr(inFile.Read(1))
 If charContainer=delim Then
   Redim Preserve wordArray(counter)
   wordArray(counter)=LTrim(RTrim(lineContainer))
   lineContainer=""
   counter=counter+1
 ElseIf charContainer="<" Then
   lineContainer=(lineContainer & "[")
 ElseIf charContainer=">" Then
   lineContainer=(lineContainer & "]")
 Else lineContainer=(lineContainer & charContainer)
 End If
Loop
Redim Preserve wordArray(counter)
wordArray(counter)=LTrim(RTrim(lineContainer))
Call writeToHTML(wordArray)
charContainer=""
lineContainer=""
counter=0
inFile.SkipLine
Loop




Call writeHTMLFooter




REM **********************************


Function writeToHTML(wordArray)


outFile.WriteLine("<tr>")
For Each i in wordArray
  If i <> "" Then
    outFile.WriteLine("<td>" & CStr(i) & "</td>")
  End If
Next
outFile.WriteLine("</tr>")


End Function


REM **********************************


Sub writeHTMLHeader


outFile.WriteLine("<html><head></head><body>")
outFile.WriteLine("<table border=1>")


End Sub


REM **********************************


Sub writeHTMLFooter


outFile.WriteLine("</table>")
outFile.WriteLine("</body></html>")


End Sub


REM **********************************




Here we've got the usual VBScript constructor, followed by a call to the writeHTMLHeader function (which we can adapt as we see fit, writes the HTML header tags) - then some code to strip the data out of CSV and back into an array.  Then for each member of the array, each value is written between <td> tags (the header function wrote the table headers).  Finally, the footer is written.  The batch file opens it in Chrome once done (change the <path>).


What does the output look like?  This:

Usage:  'go <server\instance> <script>'.


Make sure all the files are in the same directory.


Comments welcome.

All Your Data Are Belong To Us

Three Attack Vectors on SQL Server 2005  


In this post, I'm going to explore three different vectors for attacking a SQL Server 2005 database instance.  Before I begin, I must explain this isn't a hacking blog - rather, this post may benefit those who have lost, or not been given (and have a genuine need for) sysadmin credentials on a SQL instance.  Two of the vectors will assume you have local administrator or domain administrator access to the server holding the database installation.  The third assumes you have any access (and can remove data from the server, be it over the network or to USB device).

The first method to explore is the well known method of Dedicated Administrator Connection (DAC) access.  This feature was introduced by Microsoft specifically to deal with, for example, the SA password being lost, or other administrative credentials.  It assumes local/domain admin access to the server and SQL Server 2005.  It does not work with 2008 or 2008 R2 (or Denali), simply because the exploit relies on SQL 2005 including the BUILTIN\Administrators group in the sysadmin role during build time by default.

Firstly, log in to the database server using your administrative credentials.  Now open services.msc, and stop the following services (if they exist):

• SQL Server(INSTANCE Name)
• SQL Server Browser
• SQL Server Agent(INSTANCE Name)
• SQL Server VSS Writer
• SQL Server Analysis Services
• SQL Server Integration Services
• SQL Server Reporting Services
• Distributed Transaction Coordinator

Now right-click on SQL Server(INSTANCE Name) and go to Properties.

Hit Stop if not already stopped, then in the Start Parameters box, type '-m' without the single quotes.  Click Start, then OK.
Now open a command window.  Use the following command to connect to SQL Server as an administrator, substituting (SERVER NAME\INSTANCE NAME) for the appropriate parameters (without the ( and ) marks):

SQLCMD -S (SERVER NAME)\(INSTANCE NAME) -E

This should present you with the prompt 1 and a pointed bracket.
At this point you can create a new login.  Issue:
 
CREATE LOGIN [DOMAIN\YOUR_NAME] FROM WINDOWS;

GO

Substituting DOMAIN for your domain, YOUR_NAME for your login name.  Include the square brackets.
Now issue:
 
EXEC SP_ADDSRVROLEMEMBER @loginame='DOMAIN\YOUR_NAME' @rolename='sysadmin';

GO

This will associate your account with the sysadmin role.

Now exit, and restart all SQL Services including SQL Server without the -m parameter.  When you log into SQL Server Management Studio and browse the Security - Logins tree, you will see your new login and right-clicking then going to Properties, Server Roles will reveal your new sysadmin access.

The second method of attack also relies on local or domain administrator access.  You may find that for any reason (most often, BUILTIN\Administrators was deliberately excluded at build time), DAC fails.  Or another connection is hoovering up the one available administrative connection (this happens when you try to use DAC using SSMS).  If this is the case, we can hijack (if it exists for your system) any group specified as the SQL Server service account group. 

Go to Users->Manage in your Windows operating system (varies by version).  Find your local user account - if you don't have one, create one - then Properties, and in the Other field, find the MSSQL service account.  In the screenshot below (usernames etc. redacted) you will see the account highlighted:

Now hit OK, log off and log in again.  Try accessing SQL Server Management Studio using your normal Windows login credentials.  You should find you have securityadmin or sysadmin role - check the Properties of your login and Server Roles to confirm.
The third and final attack vector I will discuss assumes only that you have access to the server with any credentials.  Although this can be blocked, e.g. by folder security in Windows, you may find that the files are not protected in any way - especially in MS Windows Server 2003(!).

Open a command window on the server and issue CD \ .  Then DIR /S *.mdf > whereAreThey.log.  Open the log file in Notepad, this will show you the location of the .mdf files if they exist.  If you can't find them, try a different drive, or verify you are on the correct server:

Now navigate to the directory containing the .mdf files.  Then open services.msc and stop the SQL Server(INSTANCE Name) service.  Copy the .mdf file from the directory to the media of your choice.  Then restart the service.
Next, with your newly-copied .mdf file, install SQL Server 2005 on the target system (e.g. another workstation or server).  Use default settings for everything.  Alternatively, you can use an existing instance.  You are able to use SQL Server Express Edition providing the database you are accessing is no more than 4GB in size.

On your target system, open a command window.  Issue:
 
SQLCMD -S (SERVER NAME)\(INSTANCE NAME) -E

1> SP_ATTACH_SINGLE_FILE_DB @dbname='(DBNAME)' @physname='(PATH)\(FILENAME).mdf';

2> GO

This will bypass the need to include the transaction log file.  Now go into SSMS and open the Databases tab in the Object Explorer window.  You will see the database you attached.  Assuming you have already been given, given yourself, or own the target database instance, you will have sysadmin credentials on the new database and full access to all data within.

Be aware that this approach may fail if the source instance uses certificates (stored in the master database), or the source instance uses Transparent Data Encryption.  If this is the case your only other option might be to use a third-party password recovery tool, such as a brute-force 'John The Ripper' or Hydra attack, social hacking or something targeted from Red Gate, etc. 
One final tip - many application developers are sloppy coders.  Check the registry and/or application settings for plaintext passwords in connection strings - ASP.NET pages are a good place to find these, likewise web.config in the .NET Framework root folder.  You might find this could save you a lot of time and effort. 

Connecting a Web form to SQL Server - The Easy Way

Merry Christmas and a Happy New Year! This article is going to cover an easy yet versatile method of connecting a web form, with user input, to a SQL Server database, with the aim being to successfully move information input by the user to a structured set of tables in the database. This is a requirement of many website designers who need to capture user input for processing, to capture data such as contact information, etc. There are many methods of doing this, and this article is doing to be using the following technologies:

- HTML 4.0 and above
- .NET Framework 4.0
- Visual Studio 2005 (or Notepad, up to you!)
- ASP.NET using the Visual Basic (VB.NET) language
- Microsoft Internet Information Services on Windows 7
- Microsoft SQL Server 2008 R2 Express Edition

A quick disclaimer - the code shown below may or may not work for you and I accept no responsibility howsoever caused by the use of my code on systems not under my control either directly or indirectly. OK? :-)

Also if you're cutting/pasting remember to adjust the code indentations and line breaks as I suspect Google will mangle my formatting when I post. VB code line break character is _ and HTML/SQL doesn't need one in most cases (although it's good practice). To aid readability - SQL comment character is --, VB is '. Code is in Courier New, text in Calibri.

Firstly, I'll cover creating a (very!) basic web form to allow user input. Then, I'll show you how to con your home PC/laptop into thinking it's a web server. Next, I'll create an .ASPX page with VB code to handle the input. I'll go through creating some data validation functions too. Then, I'll cover creating a connection using ADO (ActiveX Directory Objects, part of the COM objects in Windows) to SQL Server. After, I'll cover a couple of methods of sending data to the database (and some ideas on retrieving it, too). Finally, I'll go through some ideas on improvements and other considerations such as security, encryption and client-side validation.

Okay, firstly let's define a new HTML page to capture the information. We'll be capturing a user's name, address and telephone number, date of birth and gender. Create a HTML page like the following:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
  <title>Data Collection Form</title>
</head>
<body>
  <form name = "form1" action="send.aspx" method="POST">
  <h2>Data Collection Form</h2>
  <br><br>
  <table>
  <tr>
  <td>First Name:</td>
  <td><input type="text" name="firstName" size="40" /></td>
  <td>      </td>
  <td>Last Name:</td>
  <td><input type="text" name="lastName" size="40" /></td>
  </tr><tr>
  <td>Gender:</td>
  <td><input type="text" name="gender" size="40" /></td>
  <td>      </td>
  <td>Date of Birth:</td>
  <td><input type="text" name="dateOfBirth" size="40" /></td>
  </tr><hr><tr>
  <td>Address Line 1:</td>
  <td><input type="text" name="address1" size="40" /></td>
  </tr><tr>
  <td>Address Line 2:</td>
  <td><input type="text" name="address2" size="40" /></td>
  </tr><tr>
  <td>Address Line 3:</td>
  <td><input type="text" name="address3" size="40" /></td>
  </tr><tr>
  <td>Address Line 4:</td>
  <td><input type="text" name="address4" size="40" /></td>
  </tr><tr>
  <td>Postcode:</td>
  <td><input type="text" name="postcode" size="40" /></td>
  </tr>
  </table>
  <br><br>
  <input type="submit" value="submit" />
  </body>
</html>
The form looks like this:



Next, we need to create a database. We're not going to configure it at this point - just create it. So open SQL Server Management Studio, log in as a user with sysadmin role, open a New Query window and type:

CREATE DATABASE customerInformation
GO
Now let's create some tables. We'll try and keep this to at least 1NF, meaning no duplicate values, one item per field per row and primary keys. Let's do it:

USE customerInformation
GO
CREATE TABLE personalInfo (
  customerID int IDENTITY CONSTRAINT pk_Customer_ID PRIMARY KEY
  firstName varchar(50), lastName varchar(50),
  gender varchar(6), dateOfBirth smalldatetime
)
GO
CREATE TABLE addressInfo (
  customerID int IDENTITY CONSTRAINT pk_Customer_ID PRIMARY KEY
  address1 varchar(255), address2 varchar(255),
  address3 varchar(255), address4 varchar(255),
  postcode varchar(10)
)
GO
Seasoned DBAs reading this might be up in arms now - where are the foreign keys? What about indexes? Why am I using a surrogate key? But the scope of this article is HTML to SQL linkage, so I'll leave the topic of normalisation, optimisation and good SQL coding practice alone.

Let's create the stored procedures you'll need to load data into the database. One question you could ask now is - why use stored procedures? Given that the System.String method can hold 2^31-1 characters, why bother? The answer is security - to help prevent SQL injection, sending parameters to a stored procedure at least starts to mask the process that the SP is following (although in our example, it'll only be INSERTs anyway with no validation database-side).

CREATE PROCEDURE personalInfo$Loader (
  @customerID int,
  @firstName varchar(50), @lastName varchar(50),
  @gender varchar(6), @dateOfBirth smalldatetime
)
AS
INSERT INTO dbo.personalInfo
  VALUES(@firstName, @lastName, @gender, @dateOfBirth) -- The IDENTITY
  -- attribute auto-generates customerID if NULL is passed in
GO

CREATE PROCEDURE addressInfo$Loader (
@customerID int,
@address1 varchar(255), @address2 varchar(255),
@address3 varchar(255), @address4 varchar(255),
@postcode varchar(10)
)
AS
INSERT INTO dbo.addressInfo
VALUES(@address1, @address2, @address3, @address4, @postcode) -- The IDENTITY attribute   
-- auto-generates customerID if NULL is passed in
GO
Execute these CREATE statements.

Now, we need to enable IIS for your local machine. I'm assuming you're running Windows 7 but this is possible in XP and Vista too (downloads might be required). Simply go to Control Panel -> Programs and Features, then click 'Turn Windows Features on or off'. Check 'Internet Information Services' and 'Internet Information Services Hostable Web Core' and click OK. Once installed, open IIS Manager by using Start -> Run -> iis. Expand the tree in the Connections window and right-click on Sites, click Add Web Site. Now name the site 'Data Collection Form' with DefaultAppPool. Under Content Directory point the site to the path of the html page you created above (by default, sites should be in C:\Inetpub\wwwroot). Ensure the binding is http, 127.0.0.1 on port 80. Enter the host name of your local machine (local machine name). Check Start Web Site Immediately. Click OK. Now close IIS.

Ensure you have installed the .NET Framework 4.0 package from Microsoft - if you haven't, Google it and download. Once installed, open a command window, type aspnet_regiis -i and hit Enter. This will register the .NET framework with IIS and when you now open IIS, you'll see the aspnet_client folder under the Connections tree. Finally, go to the .NET Framework 4.0 root folder (normally under C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config) and find web.config. Insert the following values into the config file after <location> and before <system.web> tags (at the top):

<connectionStrings>
  <add name="localConn"
  connectionString="Data Source=COMPNAME\NAMEDINSTANCE; Initial Catalog=DBNAME; User Id=DOMAIN\USER; Password=PASSWORD" providerName="System.Data.SqlClient" />
</connectionStrings>
Swap out CAPITAL strings above for actual values. What this will do is add a referencable connection string to the .NET framework (whether we use it or not) - we can then reference localConn as an object from ASP i.e. localConn.Open, localConn.Execute etc. This is here for completeness - we'll actually use ADO, but the choice is yours.

Save your HTML form as C:\Inetpub\wwwroot\index.html. Now go to http://127.0.0.1/index.html and you'll see your form.

Next we must create the ASPX page which will handle the data processing. It's referred to in the HTML header as send.aspx, so that's what we'll call it. Open a new text file and save it as send.aspx (in VS/Notepad). Here is the code:

<%@ Page Language="VBScript" AspCompat="true" Debug="true" %>
<%
' We WON'T use the adaptation to .NET, instead here's an alternative method using ADO:
connection = Server.CreateObject("ADODB.Connection")
connection.ConnectionString = "Data Source=COMPUTERNAME\NAMEDINSTANCE;Initial Catalog=DBNAME;User

Id='USERNAME';Password='PASSWORD';PROVIDER=SQLNCLI10"
connection.Open
Dim firstName, lastName, gender, DateOfBirth, address1, address2, address3, address4, postcode
firstName = Request.Form("firstName")
lastName = Request.Form("lastName")
' ... keep going until you get to postcode
postcode = Request.Form("postcode")
Dim personalInfoQueryString As String
Dim addressInfoQueryString As String
personalInfoQueryString = "EXECUTE dbo.personalInfo$Loader " + chr(39) + ", " + firstName + "chr(39)" _
+ ", " + lastName + chr(39) + ", " + chr(39) + gender + chr(39) + ", " + chr(39) + dateOfBirth + _
chr(39) + ";"
addressInfoQueryString = "EXECUTE dbo.addressInfo$Loader " + chr(39) + ", " + address1 + "chr(39)" _
+ ", " + address2 + chr(39) + ", " + chr(39) + address3 + chr(39) + ", " + chr(39) + address4 + _
chr(39) + ", " + chr(39) + postcode + chr(39) + ";"
connection.Execute(personalInfoQueryString)
connection.Execute(addressInfoQueryString)
response.Write("Data has been written to the database!")
connection.Close
connection = Nothing
%>
So what does it do? Firstly, it sets up an ad-hoc connection using ADO. We could have achieved this using the connection string in the .NET framework on the 'server' for more security, if we wanted to. Next, we open the connection, then define a couple of connection string objects as Strings. Then we load the values from the form using Request.Form into local variables defined as a typeless variable using Dim. Then, each string is constructed using VB.NET syntax (NOT SQL syntax!) which means that we have to specify single quotes as escape characters (hence the ASCII chr references) to avoid mis-parsing during compilation, and use the + operator for string concatenation. The _ character here is used for multi-line splits to avoid messy code. Then, we execute the string using the Execute method of the Connection object you specified. Then we close and deallocate the connection and end the code. A response is returned to the browser confirming success.

Now let's test the code. It's not unusual for newly-input code to require a debug, even when copied, so be prepared to comb your code for syntax errors, commas instead of full stops, missing apostrophes and all the other fun banana skins that development entails! Open your web form in the browser and put some sample information in. Then hit Submit. If there's a problem, debug is specified to TRUE in the ASP so you'll be able to see the stack trace and diagnose it. If not, you'll see 'Data has been written to the database.'. Go to SSMS if this is the case and SELECT * FROM dbo.personalInfo. You'll see one row returned.

This is the most basic method of linking HTML to SQL that I know. There are many more, using C#, client-side scripting, JavaScript, and other sites can tell you all about them. This method works - and is very versatile.

For example, you could add data validation. Here's a bit of code that will convert 'Yes', 'No' in different cases and in shortened forms (i.e. user types 'yes') to a bitwise (1 or 0) character. bitCollection is an object of type Microsoft.VisualBasic.Collection() which can hold variables (rather than strings, such as a string array). Preload bitCollection using Dim bitCollection As New Microsoft.VisualBasic.Collection() then bitCollection.Add(VAR_NAME), with the fields which you want converting. For the code below, imagine we have a 'Sign up for more information?' field, named (in the code) as signUpForSpam. The following code is a bit clumsy - the IFs aren't all that scalable and I could shorten the array definitions, for example - but it works:


Dim yesBoolValues(5) As String
Dim noBoolValues(5) As String
yesBoolValues(0) = "YES"
yesBoolValues(1) = "Y"
yesBoolValues(2) = "yes"
yesBoolValues(3) = "y"
yesBoolValues(4) = "1"
yesBoolValues(5) = "Yes"
noBoolValues(0) = "0"
noBoolValues(1) = "NO"
noBoolValues(2) = "N"
noBoolValues(3) = "no"
noBoolValues(4) = "n"
noBoolValues(5) = "No"
Dim yesFlag As Boolean
Dim noFlag As Boolean
Dim flag1 As Boolean
flag1 = False
yesFlag = False
noFlag = False
For Each i In bitCollection
For Each j In YesBoolValues
If (CStr(LTrim(RTrim(i))) = j) Then
yesFlag = True
End If
Next j
For Each k in NoBoolValues
If (CStr(LTrim(RTrim(i))) = k) Then
noFlag = True
End If
Next k
If ((yesFlag = False) AND (noFlag = False)) AND (flag1 = False) Then
response.write("<br><br>One or more Y/N values in the form are invalid. Please specify either Y or N.")
flag1 = True
End If
If i = Nothing Then
flag1 = True
End If
yesFlag = False
noFlag = False
Next i
For Each y In yesBoolValues
If (y = CStr(LTrim(RTrim(signUpForSpam)))) AND (flag1 = False) Then
= 1
End If
Next y
For Each n In noBoolValues
If (n = CStr(LTrim(RTrim(signUpForSpam)))) AND (flag1 = False) Then
'PUT YOUR VAR NAME HERE = 1
End If
Next n
If you're wondering what 'flag1' is, it's for encapsulating the connection.Execute commands later on. Say you didn't want to execute the query strings unless the validation passes (a reasonable assumption). You could use an IF ... THEN ... ELSE structure e.g.

If flag1 = True Then
'Connection.Execute(queryString) goes here
response.write("Successful!")
Else
response.write("Failed!")
End If
For a recent project, I used seven different types of validation and multiple flags amalgamated into a 'master flag' which encapsulated the final queryString execution - in this way, only if all validation passed would the query strings execute.

Another data validation function (courtesy of VB) - I needed a way to check that a date was valid and within a range. After an hour or so of trying to put together a string parsing function (convert date type to string; parse string, separating by /; convert split strings to int; check int within range (1-31),(1-12),(1980-2100)), I thought - there must be a better way. Here it is:

Dim fooDate As Date 'this is a throwaway value
Dim flag2 As Boolean
flag2 = False
For Each i In smalldatetimeCollection
If Not Date.TryParse(CStr(i), fooDate) AND (flag2 = False) Then
response.write("One or more dates entered are invalid. Use format DD/MM/YYYY.")
flag2 = True
End If
Next i
Again, preload smalldatetimeCollection with the fields you want to validate first.

What about checking for NULLs? Remember to correlate your NULLable values (ones that are allowed NULL, in our example it's all of them accessible to VB - the PRIMARY KEY is looked after by the DB, not accessible to VB) with the ones set in the DB. For example, in a three-column table:

EmployeeID INT UNIQUE NOT NULL, Name VARCHAR(255) NOT NULL,
Salary MONEY NOT NULL, Grade VARCHAR(2)
Grade is allowed NULLs. So notNullableCollection in the following code should contain the other three variables:

Dim flag3 As Boolean
flag1 = False
For Each i In notNullableCollection
i = CStr(i)
If (i = Nothing OR (LTrim(RTrim(Len(i) = 0)))) AND flag1 = False Then
response.write("<h2><font color=Red face=Calibri>There are mistakes in the form.</font></h2><br>")
response.write("<h3><font color=Red face=Calibri>Use the <img src=backbutton.jpg> button to return and try

again.</font></h3><br><br>")
response.write("Fields marked with * cannot be left blank.")
response.write("<br><br>")
flag3 = True
End If
Next i
Here's something more complex for checking a valid time format, HH:MM. In VB it's passed in as type String, passed out as type smalldatetime to SQL:

' checks for valid time in HH:MM in 24-hr format
Dim hoursOKFlag As Boolean
Dim minsOKFlag As Boolean
Dim flag4 As Boolean
Dim allowableHours() As String =

{"00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23"}
Dim allowableMinutes() As String =

{"00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25

","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","5

1","52","53","54","55","56","57","58","59"}
flag4 = False
For Each k In timeCollection
hoursOKFlag = False
minsOKFlag = False
k = CStr(k)
If k <> Nothing Then
Dim splitTime As String() = k.Split(New [Char]() {":"})
For Each i In allowableHours
If splitTime(0) = i Then
hoursOKFlag = True
End If
Next i
For Each i In allowableMinutes
If splitTime(1) = i Then
minsOKFlag = True
End If
Next i
If (hoursOKFlag = False OR minsOKFlag = False) AND flag4 = False Then
response.write("<br><br>Time format in one or more fields is incorrect. Use HH:MM.")
flag4 = True
End If
End If
If k = Nothing AND flag4 = False Then
response.write("<br><br>Time format in one or more fields is incorrect. Use HH:MM.")
flag4 = True
End If
Next k
The Collection to preload here is timeCollection.

String lengths can be checked using If..Then and the Len() function. Or why not try client-side scripting? Functions in the HTML can check for validity, especially with HTML 5 - there's more information from Microsoft here -> http://msdn.microsoft.com/en-us/library/yb52a4x0.aspx. Do remember that database-side validation is fine - but put some try-catch exception handling in your application code first, otherwise the database errors will be fed in a raw format to the user!

And you could try using the DataReader object too, to load the data from the database. Or use SQL Server Reporting Services to generate some BI (business intelligence - a dichotomy, perhaps?) from the gathered data.

As you can see, linking HTML code to SQL can be as easy or complex. Hopefully the method I've outlined has given you the basic idea and you will be able to experiment with different implementation methods.

And once again - have a happy New Year.

BLOG POST: Centralising a Backup Solution for a Distributed SQL Server 2005 Infrastructure

Recently, following a number of alerts on one of my client's SQL boxes, I discovered that their backup methodology was slipshod at best.  Eight servers in total had a mixture of SIMPLE recovery and FULL recovery models, weekly, ad-hoc and transaction log backups, no backup management in place other than poorly configured SSIS packages in SSMS Maintenance plans and no method to perform point-in-time restores (or even guarantee the validity of the 'last known good' backup set).
This raised an interesting question.

What is the 'best' way to configure backups?

Arguably it's entirely down to the requirements of the customer - which boils down to two questions.  How much data can you afford to lose?  And how long can you wait for a data restore?  These two questions will clearly depend on the industry in question - a car assembly plant, for example, will require a point-in-time restore capability carried out in the shortest possible period of time.  On the other hand, 'Nan's Cake Shop' with a SQL Server Express backend to an order management application will probably be able to cope for a week or two unhindered.


My client's requirements were ambiguous - while they were able to continue without their IT for a short period in the event of data loss, the ability to restore was important.  And although they are a non-profit organisation, there would be financial and commercial implications to the loss of any database functionality.


The physical structure was one increasingly familiar to many DBAs today - VMWare using ESX.  Six of the seven affected servers were VM, with just one physical box.  All seven shared a SAN and a Cisco switch.  There were four physical ESX servers with the entry point (using VSphere) on a different server, currently the recipient of backup files from other machines and a data repository.  Microsoft's official stance is one of non-support for ESX-based VMWare, but they are coming around to the idea of virtualisation with technology such as SQL Azure, and extended support for the 'cloud' with Denali.


For my client, I chose a centralised design based on the classical 'star' formation familiar to anyone who's ever taken a networking course.  At the centre, I chose the server with the largest amount of assigned HDD space (VM, remember!) with the 'satellites' being the SQL Servers, which all share a domain.  For each satellite, I configured a Maintenance Plan comprising of six sub-plans - one, for a full local backup.  Two, for a local transaction log backup.  Three, for a maintenance cleanup for local full backup files.  Four, for the maintenance cleanup of local transaction log backup files.  Five, for the remote cleanup of the hub server full backup files.  And six, for the remote cleanup of the hub server transaction log backup files.  Any databases in SIMPLE mode went into the FULL recovery model.


Next, I scheduled each sub-plan according to purpose.  The transaction log files were scheduled for local backup every 15 minutes at 00, 15, 30, 45.  The full backups were scheduled daily at 02:00.  The cleanups were scheduled daily with a 4-day retention (at 01:00 to prevent disk use spike) for full backups, hourly with a 2-day retention for transaction log backups, daily at 03:00 with a 10-day retention for remote full backups and hourly with a 5-minute offset for remote transaction log backup cleanup (05, 20, 35, 50). 


Next, I wrote a couple of simple batch files to XCOPY /E /Y from the source satellite directories to the target hub directory, and mapped the appropriate network drives, scheduling the batch files to run daily (for full backups) and every 15 minutes with a 10-minute offset (00, 10, 20, 30, 40, 50) for transaction log files.  These jobs were to load the backup files onto the hub disk storage (which is actually on the same SAN as the satellites!)


Finally, I configured a range of alerts and operators in SQL Server on each satellite to send e-mail push notifications out should the appropriate BACKUP FAILED event be captured in the error log - these alerts will also notify on disk space consumption, which is the largest risk of my approach.  So no need for anyone to go and check these backups more than once a quarter.

Careful backup management will normally depend on interaction with your SAN admin to determine the best solution.  There are many flaws with mine - for example, the constant flow of data actually travels out into the domain (past the switch) to the domain controller where it is re-routed back to servers which share the same disks!  Although what I've put together works, it won't work for everyone and while it will scale, it will do so at the expense of bandwidth.  It also has redundant feedback loops, with every satellite responsible for tidying the hub when just one could suffice.  Others more imaginative than I will quickly point out that this system could have been administered using Policy Management, PowerShell or any one of a number of other methods.  No approach is invalid (unless it actively causes harm) but the best interests of the clients' systems should always be borne in mind.


Many DBAs in the field will find themselves dealing with a wide variety of different systems often using very different technology.  In a previous role, I have found myself dealing with Oracle 7(!) databases on an old, unwieldy Sun Solaris server whose only job was to act as a message broker.  In the very next call, I'd be dealing with a BladeServer running Windows Server 2008 R2 and supporting a dozen different MS SQL Server instances.  So the answer to the question asked earlier - what's the 'best' way of configuring a backup solution - there is no 'best' way, although there are certainly best practices and principles to be followed. 

I believe that the most important of these are scalability, reliability and fitness for purpose.

More information on configuring SQL Server backups can be found here: 

http://msdn.microsoft.com/en-us/library/ms175477.aspx