Encrypt/Decrypt connection strings in web.config

const string PROVIDER = "DataProtectionConfigurationProvider";
const string PROVIDER = "RSAProtectedConfigurationProvider";

Configuration ObjConfiguration =
WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);

ConnectionStringsSection cnStrSettings = ObjConfiguration.ConnectionStrings;

//Encrypt the Connection Strings Section
cnStrSettings.SectionInformation.ProtectSection(PROVIDER);

//Decrypt the Connection Strings Section
if (cnStrSettings.SectionInformation.IsProtected)
{
cnStrSettings.SectionInformation.UnprotectSection();
}
ObjConfiguration.Save();

aspnet_regsql with SQLExpress database

Creating a new DB in VisualStudio.NET 2005 is as simple as "Select APP_DATA node -> Add New Item -> Sql Database" and wah-lah you have a new aspnet.mdf file located in your APP_DATA folder. However, when you run the tool ASPNET_REGSQL.exe in Wizard Mode (E.g. using the "-W" switch) there is no way to specify a SQLEXPRESS attached database - it only seems to support SQL Server 2005 (and earlier) database servers.

So, after several attempts, I finally figured-out the "right" way to do this:

aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\MyProject\APP_DATA\aspnetdb.mdf"

This will connect to the local SQLEXPRESS engine and attach the MDF file passed in the "-d" switch then create the appropriate objects in the DB.
================================================================

aspnet_regsql.exe -S server -d database -E -A all

While this concept still applies for SQL Server 2005 Express Edition, it can be a little harder to get the server and database names right. What database server is SQL Server Express installed on? And what's the database name for a .MDF file in the App_Data folder?

Assuming you are working on an ASP.NET application locally, the server name will be: localhost\SQLExpress

The database name is (and here's it can get a bit tricky), is the path to the MDF file when it was created. So, say that you have an ASP.NET application created in the classroom lab at C:\Labs\Website\App_Data\MessageBoard.mdf. The name of the database is C:\Labs\Website\App_Data\MessageBoard.mdf, meaning you could install the membership services from the command-line using:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Labs\Website\App_Data\MessageBoard.mdf” -E -A all

Now, imagine that you zip up your files onto a USB keychain drive, go home, and copy your project files to C:\Home\Website. Now, if you wanted to create the services, you'd think you'd just type in:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Home\Website\App_Data\MessageBoard.mdf” -E -A all

Ah, but the database name is C:\Labs\Website\App_Data\MessageBoard.mdf. Eep. So when you run the above command the database can't be found and cryptic error messages abound. Essentially, it can't find the database C:\Home\Website\App_Data\MessageBoard.mdf so it tries to create a database file in the default directory (%PROGRAM FILES%\Microsoft SQL Server\MSSQL.1\DATA) with the filename C:\Home\Website\App_Data\MessageBoard.mdf. This, of course, causes problems since that's not a valid filename. Ick.

So how do we fix this? There are a couple optios. The easiest is probably to download the (free) SQL Server 2005 Management Studio Express program and attach the database file. Then, from the Properties pane you can see the database name. You can then use this with aspnet_regsql.exe. (You could also rename the database at this point...)

If you want to be 3l33t you can use sqlcmd, attach the database (sp_attach_db) giving it a friendly name, which you can then use to run the aspnet_regsql.exe command line program against. Something like:

sqlcmd -S localhost\SQLExpress -Q “EXEC sp_attach_db 'Foobar', N'pathToDBfile'”

And then:

aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all

Adding Asc/Desc Icon on Table Headers for Sorting

ListItemType itemType = (ListItemType)e.Item.ItemType;
// Don't bother too much for the footer, and separator items

if(itemType == ListItemType.Separator || itemType == ListItemType.Footer)
{
}
else if (itemType == ListItemType.Header)
{

TableCell TCell;
if(TCell != null)
{
// make a new label
Label sortSymbol = new Label();
sortSymbol.Font.Name = "Webdings";
sortSymbol.Font.Size = 10;

if(this.SortAscend == "Asc")
{
// Ascending
sortSymbol.Text = "5";
sortSymbol.ForeColor = System.Drawing.Color.Red;
theCell.Controls.AddAt(0, sortSymbol);
}
else
{
// Descending
sortSymbol.Text = "6";
sortSymbol.ForeColor = System.Drawing.Color.Blue;
theCell.Controls.Add(sortSymbol);
}

}

SPLIT function - TSQL

CREATE FUNCTION SPLIT (
@str_in VARCHAR(8000),
@separator VARCHAR(4) )
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN

DECLARE
@Occurrences INT,
@Counter INT,
@tmpStr VARCHAR(8000)

SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable
VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0
BREAK

END
RETURN
END

Encryption in SQL2005

I found this nice article on Encryption in SQL2005.

http://www.databasejournal.com/features/mssql/article.php/3461471 - Authentication
http://www.databasejournal.com/features/mssql/article.php/3481751 - Authorization

http://www.databasejournal.com/features/mssql/article.php/3483931 - Encryption

http://www.databasejournal.com/features/mssql/article.php/3488046 - Security

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/3058/

As a matter of fact we already have password hashing capability in SQL2000 itself, the PWDENCRYPT function is available to create a one-way hash.

In 2005 there are the EncryptByKey, EncryptByCert, EncryptByAsmKey and EncryptByPassPhrase methods that can be used for encrypting data.

For passwords the encryptByCert / EncryptByAsmKey or EncryptByPassPhrase can used which provide enough security.

EncryptByKey is symmetric key encryption which is usually combined with Certificates to encrypt the Symmetric key used for encypting large amounts data.

At the root level is the Service Master key which is automatically created during SQL2005 installation.

Then we have a Database Master Key which needs to be created using DDL statements for each Database on the SQL Server Instance. The database master key is created using the service master key along with an optional password.

• create master key encryption by password = 'SecretPassword' ----this creates the DB master key
• create certificate rbg_cert with subject = 'cert for rbg' ----creates a certificate using the DB Master key
• create certificate rbg_cert encryption by password = 'secret' with subject = 'cert for rohit' ----- this creates a certificate using password as the Private Key instead of the DB master key.
• create user rbg for certificate rbg_cert ----- creates the PROXY user
• create login rbg_login from certificate rbg_cert --- creates a SQL login -- note logins are created in master DB, thus a rbg_cert should exist in master DB before you can run this command

Important system tables are

1. sys.symmetric_keys - stores master and symmetric keys
2. sys.databases --- list of all db's in the server
3. sys.certificates -- lists certificates in a particular DB
4. sysusers --- lists users in a particular DB, syslogins --- all logins
   

Then Certificates and Asymmetric keys are created using the database Master key and then we use the Encryption functions to encrypt and decrypt data/passwords. The EncryptByKey and EncryptByPassPhrase are symmetric encryption mechanisms which do not involve using the Database master key

Script to Temporarily disable and enable constraints

http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm#s4

/*To disable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To disable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To disable constraints on tables starting with a particular pattern*/
GO

/*To enable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To enable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To enable constraints on tables starting with a particular pattern*/
GO

Quick Search within all stored procedures

/* Method 1 :: For Master Database */

Select
distinct(SO.[name]) as 'Stored Procedure Name'
From
SysComments SC,
SysObjects SO
Where
SO.ID = SC.ID and
cateGory <> 2 and
PatIndex( @searchString, text) > 0
Order By
[name]
End
Else
Begin

/* Method 2 :: For Individual Database
Double check whether you have removed the prefix "sp_" from the SP name.
*/

Select
Routine_Name as 'Stored Procedure Name'
From
InformatiOn_Schema.Routines
Where
Routine_type = 'procedure' and
ObjectProperty(Object_Id(Routine_Name),'IsMsShipped') = 0 and
PatIndex( @searchString, Routine_DefinitiOn) > 0
End

If @@Error <> 0
Return (1)
Return 0
Go

sp_executesql for dynamic stored procedures with parameters

DECLARE @sql nvarchar(2000)

DECLARE @param nvarchar(400)

SET @param = N' @ProductID INT, @ProductInventoryStatusID INT, @ManagedByPersonID INT'

SET @sql = N' INSERT INTO #tbRangesStart
SELECT DISTINCT t1.serialnumber
FROM productinventory t1
LEFT OUTER JOIN productinventory t2 ON t1.serialnumber - 1 = t2.serialnumber AND t1.productid=t2.productid
WHERE '


DECLARE @where nvarchar(1000)

IF @ProductID IS NOT NULL
SET @where = N' t1.productid = @ProductID AND'
ELSE
SET @where = N' '

IF @ProductInventoryStatusID IS NULL and @ManagedByPersonID IS NULL
SET @where = @where + N' t2.serialnumber is null ORDER BY t1.serialnumber'

IF @ProductInventoryStatusID IS NOT NULL and @ManagedByPersonID IS NULL
SET @where = @where + N' t1.productinventorystatusid = @ProductInventoryStatusID AND (t2.serialnumber is null OR (t1.productinventorystatusid = @ProductInventoryStatusID AND t2.productinventorystatusid <> @ProductInventoryStatusID)) ORDER BY t1.serialnumber'

IF @ProductInventoryStatusID IS NULL and @ManagedByPersonID IS NOT NULL
SET @where = @where + N' t1.ManagedByPersonID = @ManagedByPersonID AND (t2.serialnumber is null OR (t1.ManagedByPersonID = @ManagedByPersonID AND t2.ManagedByPersonID <> @ManagedByPersonID)) ORDER BY t1.serialnumber'

IF @ProductInventoryStatusID IS NOT NULL and @ManagedByPersonID IS NOT NULL
SET @where = @where + N' t1.productinventorystatusid = @ProductInventoryStatusID AND t1.ManagedByPersonID = @ManagedByPersonID AND (t2.serialnumber is null OR (t1.productinventorystatusid = @ProductInventoryStatusID AND t2.productinventorystatusid <> @ProductInventoryStatusID) OR (t1.ManagedByPersonID = @ManagedByPersonID AND t2.ManagedByPersonID <> @ManagedByPersonID)) ORDER BY t1.serialnumber'

SET @sql = @sql + @where
EXEC sp_executesql @sql, @PARAM, @ProductID, @ProductInventoryStatusID, @ManagedByPersonID

Insert Rows Generator

This stored proc is used to generate INSERT STATEMENTS for inserting rows into a Table:

CREATE PROC sp_InsertGenerator
(@tableName varchar(100)) as
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(4000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(4000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END
SET @string=@string+@colName+','
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
print @query
--exec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO

Generating a range of Serial numbers in TSQL

The following helps in automatically generating a range of SerialNumbers in sequence:

DECLARE @SerialNumberStart INT
DECLARE @SerialNumberEnd INT
SELECT SerialNumberStart = 99900, SerialNumberEnd= 99999

DECLARE @tbNumberBase TABLE
(
Seq INT
)
INSERT INTO @tbNumberBase
SELECT digits FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)
Dig (digits)
INSERT INTO ProductInventory
(
ProductID,
SerialNumber,
Quantity
)
SELECT
@ProductID,
SerialNumber,
@Quantity
FROM
(SELECT
@SerialNumberStart
+ HundredThousands.Seq * 100000
+ TenThousands.Seq * 10000
+ Thousands.Seq * 1000
+ Hundreds.Seq * 100
+ Tens.Seq * 10
+ Units.Seq Num
FROM
@tbNumberBase HundredThousands
CROSS JOIN @tbNumberBase TenThousands
CROSS JOIN @tbNumberBase Thousands
CROSS JOIN @tbNumberBase Hundreds
CROSS JOIN @tbNumberBase Tens
CROSS JOIN @tbNumberBase Units)
TableAlias (SerialNumber)
WHERE SerialNumber <= @SerialNumberEnd
ORDER BY SerialNumber

Parameterized Properties in C#

To create Parameterized Properties in C# one can only use a Indexer (the default parameterized property... this[]). However if we wanted to store an array of a particular type one could use the following which would be a parameterized property itself.:

public string[] CardsDescription
{
get
{
if (this.ViewState["CardsDescription"] == null)
{
this.ViewState["CardsDescription"] = new System.String[3];
return (string[])this.ViewState["CardsDescription"];
}
else
return (string[])this.ViewState["CardsDescription"];
}
set
{
this.ViewState["CardsDescription"] = value;
}
}

Thus when setting values you would use

int index = 0;
foreach(System.Data.DataRow dr in productDT.Rows)
{
int productID = int.Parse(dr["ProductID"].ToString());

this.CardsDescription[index] = dr["Description"].ToString();

index += 1;
}