This article describes how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition if you are using Microsoft SQL Server 2000 or Microsoft SQL Server 7.0.

**SQL Server Integration Services is not available in SQL Server Express or Workgroup editions.**

How to determine which version of SQL Server 2005 is running

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:

• The product version (for example, 9.00.1399.06).
• The product level (for example, RTM).
• The edition (for example, Enterprise Edition).

For example, the results resemble the following:

The following table lists the Sqlservr.exe version number:

How to determine which version of SQL Server 2000 is running

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:

• The product version (for example, 8.00.534).
• The product level (for example, “RTM” or “SP2″).
• The edition (for example, “Standard Edition”). For example, the results resemble the following:8.00.534 SP2 Standard Edition

The following table lists the Sqlservr.exe version number:

How to determine which version of SQL Server 7.0 is running

To determine which version of SQL Server 7.0 is running, connect to SQL Server 7.0 by using Query Analyzer, and then run the following code:

SELECT @@VERSION

The results resemble the following:

Microsoft SQL Server  7.00 - 7.00.623 (Intel X86)
        Nov 27 1998 22:20:07
        Copyright (c) 1988-1998 Microsoft Corporation
        Desktop Edition on Windows NT 5.1 (Build 2600: )

Note In this example, the version number is 7.00.623.

Use the version number in the following table to identify the product or service pack level:

If the version number that is reported by @@VERSION is not listed in this table, SQL Server is running with a hotfix or a security update build. For example, if @@VERSION reports a version number of 7.00.859, you are running SQL Server 7.0 SP2 with a hotfix installed. The version number increases with each new version of the Sqlservr.exe executable file. See to the Readme.txt file for your hotfix or security update for more information.

How to determine which version of SQL Server 6.5 is running

To determine which version of Microsoft SQL Server 6.5 is running, connect to SQL Server 6.5 by using Isql_w, and then run the following code:

SELECT @@VERSION

Use the version number in the following table to identify the product or service pack level:

If the version number that is reported by @@VERSION is not listed in this table, SQL Server is running with a hotfix or a security update build. The version number increases with each new version of the Sqlservr.exe executable file. See to the Readme.txt file for your hotfix or security update for more information.

How to determine which edition of SQL Server is running

If you are not sure about what edition of SQL Server that you are running, the last line of output that is returned by @@VERSION reports the edition to which you have connected. The example that is used in this article is the Standard Edition of SQL Server 2000 on Windows NT 5.0 (Build 2195: Service Pack 2).

Note The build and service pack information that is provided earlier is for the operating system, not for SQL Server.

Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

If you happen to notice that your MSSQL database/s are stuck in suspect mode, you can easily run a query that will process the database and restore it for you (restore the status, not the actual database).

To do this, you simply need to run the following query. Keep in mind you will need to change our “yourdatabasename” for the actual database name.

EXEC sp_resetstatus yourdatabasename;
ALTER DATABASE yourdatabasename SET EMERGENCY
DBCC checkdb(yourdatabasename)
ALTER DATABASE yourdatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (yourdatabasename, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourdatabasename SET MULTI_USER

This will correct the issue and you can continue using the database.

Brief Description

* If you are not already familiar with SQL injection I would recommend reading http://en.wikipedia.org/wiki/SQL_injection *

The recent injection attacks that have been seen against ASP and ASP.Net coded sites takes advantage of vulnerabilities in improperly coded sites. These attacks can be mitigated by simply running any user input that can come in contact with the database through a sanitization process, and this does not apply to .Net and ASP code but any language. Below are examples of how to protect your code from Injection attacks.

If you have are dealing with a URL like http://mysite.com/somepage.aspx?page=2 instead of doing:

Dim page_num as String
Dim SqlQuery as String

SSN = Request.QueryString(“page“)
SqlQuery = “SELECT au_lname, au_fname FROM authors WHERE au_id = ‘” + page_num + “‘“

1. Write your dynamic queries using parameterized queries

Dim SSN as String = Request.QueryString(“page“)

Dim cmd As new SqlCommand(“SELECT title, content FROM page WHERE p_id = @p_id”)
Dim param = new SqlParameter(“p_id“, SqlDbType.Int)
param.Value = SSN
cmd.Parameters.Add(param)

2. Set the length of the input data

If you know that the length of the input data will not be longer than a set amount then you should constrain it to that length. In this case we are going to constrain the page length to 4 characters which will give us up to 9999 pages.

Dim page_num as String = Request.QueryString(“page“)

Dim cmd As new SqlCommand(“SELECT title, content FROM page WHERE p_id = @p_id“)

‘Here we are setting the max length to 4 characters
Dim param = new SqlParameter(“p_id“, SqlDbType.Int, 4)
param.Value = page_num
cmd.Parameters.Add(param)

3. Sanitize any escape characters from the SQL query

Dim page_num as String = SafeSQLLiteral ( Request.QueryString(“page“) )

Dim cmd As new SqlCommand(“SELECT title, content FROM page WHERE p_id = @p_id“)
Dim param = new SqlParameter(“p_id“, SqlDbType.Int, 4)
param.Value = page_num
cmd.Parameters.Add(param)

.

.

.

Private Function SafeSQLLiteral(ByVal inputSQL as String) as string

Return inputSQL.Replace(“’”, “’’”);

End Function

More information and code examples available at:

Coding techniques for protecting against SQL injection - http://forums.asp.net/t/1254125.aspx

SqlParameter Class - http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx?wt.slv=rightrail

SqlDbType Enumeration - http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

Protect From SQL Injection in ASP.NET - http://msdn.microsoft.com/en-us/library/ms998271.aspx

Go to http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx and place the code for the SQL Checker into SqlCheckInclude.asp with one edit, remove `”@”,` from the end of the first line in the BlackList Array. Next place the error page code in ErrorPage.asp at the root of your site. These two file can also be found at http://jamiemoney.com/downloads/files/ASPFormChecker.zip

Place an include for the SQL Checker at the top of any ASP form page, for example

<!–#include file=”SqlCheckInclude.asp”–>

<%

response.write(request.form(”FName“))

response.write(” ” & request.form(”LName“))

%>

<FORM METHOD=”POST“>

First Name: <INPUT NAME=FName></INPUT><BR>

Last Name: <INPUT NAME=LName></INPUT><BR>

<BUTTON TYPE=SUBMIT>Submit</BUTTON>

</FORM>

This will catch anything in the black list array and throw and redirect to the error page if it is detected including SQL commands and Escape characters.

More information and code for preventing SQL Injection and Cross Site Scritping:

How to prevent cross-site scripting - http://support.microsoft.com/kb/252985/

PHP like all other languages needs to have any user input sanitized there is a library available at http://superb-west.dl.sourceforge.net/sourceforge/owasp/owasp-php-filters.zip when included with your code will provide a simple way of sanitizing data for different uses. This can be used for interaction with a SQL database but its recommended that any user input field be sanitized as it will help prevent not only SQL injection but Cross Site Scripting as well.

Using the sanitization library above is very simple If you have are dealing with a URL like http://mysite.com/somepage.php?page=2

Instead of doing:

<?php

$con = mysql_connect(“db_server”, “user”, “pass”);

mysql_select_db(“DatabaseName”, $con);

$result = mysql_query(”SELECT * FROM Pages Where PageID=’“.$_GET['page'].”‘“);

mysql_close($con);

?>

Include the Library in your code:

<?php require_once(”includes/sanitize.php“); ?>

Pass the data you want to sanitize to the proper filter:

<?php

$con = mysql_connect(“db_server”, “user”, “pass”);

mysql_select_db(“DatabaseName”, $con);

//We pass the value for page from the Get to an sanitize for only integers

$page = sanitize_int($_GET['page']);

$result = mysql_query(”SELECT * FROM Pages Where PageID=’“.$PageID.”‘“);

mysql_close($con);

?>

Now the get field has been stripped of anything that is not an integer.

PHP Sanitize Library Function Listing

· sanitize_paranoid_string($string) — returns string stripped of all non alphanumeric

· sanitize_system_string($string) — returns string stripped of special characters

· sanitize_sql_string($string) — returns string with slashed out quotes

· sanitize_html_string($string) — returns string with html replacements for special characters

· sanitize_int($integer) — returns ONLY integer (no extraneous characters)

· sanitize_float($float) — returns ONLY float (no extraneous characters)

Suppose we have a main page that lists news articles for an organization and links to the articles with a URL similar to this: http://mysite.com/articles.cfm?articleid=2

On your articles.cfm page you might have a query that looks like this:

<cfquery name=”GetArticle” DSN=”#Request.DSN#”>

SELECT ArticleTitle, ArticleAuthor, ArticleBody

FROM Articles

WHERE ArticleID = #URL.articleid#

</cfquery>

This is a pretty standard select, except for the where clause. Because we are using a URL variable it would be easy for someone to add on to our URL: http://mysite.com/articles.cfm?articleid=2;DROP TABLE Articles

Now your cfquery looks like this:

<cfquery name=”GetArticle” DSN=”#Request.DSN#”>

SELECT ArticleTitle, ArticleAuthor, ArticleBody

FROM Articles

WHERE ArticleID = #URL.articleid#; DROP TABLE Articles

</cfquery>

With this method, someone can easily drop tables, or insert mallicious scripts into you article’s body. There are two methods to protect yourself from this.

DataSource Setup

Probably the easiest solution in ColdFusion without having to rewrite all your queries is to setup a separate DataSource or DSN. Your application would then have a DSN for password protected pages where authenticated users can make changes to the database that would have full permissions. A second DSN would then be created which only has SELECT rights to be used on your public facing pages. This can be done by following these steps:

1. Open your DataSource in the ColdFusion Administrator and click the “Show Advanced Settings” button.

2. Under the Allowed SQL section, make sure only SELECT is checked off

3. Click Submit to save your DataSource.

If anyone tries to add something other than a Select statement to your query strings, ColdFusion will throw an error before reaching your database.

Cfqueryparam

The second option is to use the cfqueryparam tag whenever you are getting dynamic data from a form or URL and passing it to your database.

Our example from before would turn out to be:

<cfquery name=”GetArticle” DSN=”#Request.DSN#”>

SELECT ArticleTitle, ArticleAuthor, ArticleBody

FROM Articles

WHERE ArticleID = <cfqueryparam value=”#URL.articleid#” cfsqltype=”cf_sql_integer”>

</cfquery>

Now if some unscrupulous visitor attempts to add an Insert or Drop command, they will be given a ColdFusion error message instead. As always, surrounding queries in cftry tags is useful to keep prying eyes form seeing the details of your error message.

The cfqueryparam tag supports the following data types:

• CF_SQL_BIGINT
• CF_SQL_BIT
• CF_SQL_CHAR
• CF_SQL_BLOB
• CF_SQL_CLOB
• CF_SQL_DATE
• CF_SQL_DECIMAL
• CF_SQL_DOUBLE
• CF_SQL_FLOAT
• CF_SQL_IDSTAMP
• CF_SQL_INTEGER
• CF_SQL_LONGVARCHAR
• CF_SQL_MONEY
• CF_SQL_MONEY4
• CF_SQL_NUMERIC
• CF_SQL_REAL
• CF_SQL_REFCURSOR
• CF_SQL_SMALLINT
• CF_SQL_TIME
• CF_SQL_TIMESTAMP
• CF_SQL_TINYINT

· CF_SQL_VARCHAR

(http://www.mssqltips.com)

Problem
Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail.  This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work.  With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain.  So how do you setup Database Mail?

Solution
There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server 2005 or by using SQL Server Management Studio.  For this exercise we will walk through how to setup Database Mail by using the GUI.

To setup Database Mail, connect to your server and expand the Management node of the tree and then right click on “Database Mail”.

Then select “Configure Database Mail’ and you will get the following welcome screen and the click “Next”.

The following screen will appear and select “Set up Database Mail by performing…” and click “Next”.

If Database Mail has not been enabled, you will get this following screen. Just click “Yes” to enable it.  If it has already been enabled this screen will not appear.

Enter in a name for the Profile and also a description and click “Add…”

The following screen will appear.  Fill out the details for your mail account that will be used to send out email from SQL Server.  When you are done click “OK”.

After you click “OK” you will be brought back to this screen and the SMTP details will now show for the account you just setup.  Click “Next” to continue.

On the next screen you will see the name of the profile that you just setup.  Click on the checkbox to allow this to be a Public profile and also select “Yes” for the default profile and then click “Next”.

The following screen has some additional parameters that can be set to control how mail is sent.  You can make changes or leave the defaults.  When you are done click “Next”.

A summary screen will appear that shows you all of the options that were selected.  If everything is correct click “Finish” or click “Back” to go back and make changes.

When you click “‘Finish” the next screen will appear that shows you the status of installing Database Mail. When this has finished just click “Close” to close this screen.

To test Database Mail, right click on Database Mail and select “Send Test E-Mail”.

Fill in a “To:” email address and change the body of the email if you want and then click “Send Test E-Mail”.

After you have sent the email you will get this message box to confirm if the email was received or not.  If it was you can click “OK” to close the screen or click “Troubleshoot” which will launch the help information to see what the issue may be and how it can be resolved.

That’s all there is to it.  As I mentioned before this can also be setup by using stored procedures. To look at this approach take a look at this article Database Mail in SQL Server 2005.

Next Steps

• Setting up Database Mail is not that complicated and it is much easier then SQL Mail. Take the time to see how this new approach to mail can work in your environment
• After you setup Database Mail don’t forget to setup your operators, alerts and SQL Agent alert settings