Database Schema definetion

Today's post is about how we can get schema definition of the current db and how we can get tables names, its columns names, Pk & Fk columns

to get the schema definition of Db

SELECT f.name AS ForeignKey,
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
JOIN sys.objects o
ON f.parent_object_id = o.object_id
ORDER BY SchemaName ASC,TableName ASC,ColumnName ASC

To get the list of Tables in Database

select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE' order by TABLE_NAME

To get the columns name, Data type, Length, Null able of a Table

select Column_Name, data_Type, character_maximum_length,IS_nullable
from information_schema.columns
where Table_Name = ''

Get Pk Columns of a Table

EXEC sp_Pkeys 'Table name'

To get the Fks of a table you can use the upper most query

To get the all Sps of the data base use the query

SELECT *
FROM sys.procedures;

To get only user defined Sps use the following query

Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

To get all User defined function from database, use query below

SELECT name AS [UDFName]
,create_date as [CreationDate]
,modify_date as [ModificationDate]
,type_desc as [FunctionType]
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'
ORDER BY [UDFName]

To get any Constraints on Table use the following query

EXEC sp_helpConstraint 'Table Name'

Customize search results and show in Find result window

Some times you annoyed about the VS show search results like entire file path scroll over to see the name of the file and resultant words

Here’s what you can do.
Note: These involve modifying registry settings. Please use at your own risk!
You do not need to restart VS

1. Go to HKCU\Software\Microsoft\VisualStudio\9.0\Find
2. Add a new string called Find result format with a value of $f$e($l,$c):$t\r\n where

$f is the filename
$e is the extension
$l is the line
$c is the column
$t is the text on the line

Now let’s take a look at that Find Results window again:

And here’s the full list of items you can specify in the registry

Files
$p path
$f filename
$v drive/unc share
$d dir
$n name
$e .ext

Location
$l line
$c col
$x end col if on first line, else end of first line
$L span end line
$C span end col

Text
$0 matched text
$t text of first line
$s summary of hit
$T text of spanned lines

Char
\n newline
\s space
\t tab
\\ slash
\$ $

Remove items from the recent project OR recent file menu.

Do you ever notice? when you open a VS solution or a file in VS, it is added in the File->Recent Files or File->Recent Projects menu. what if i change the solution name or move/delete the solution from the directory and go to the VS and try to open the solution while it is actually moved to some other directory or deleted and the following message is shown, quite some times fighting. What to do it should not be in the Recent file/project list

"The project file or web has been moved or deleted and is not in you computer"

Go to Start->Run->regedit then select the following for project list
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\ProjectMRUList

for files list select

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\FileMRUList

you will see all recently visited project/file of VS. Simply delete any one to have it removed from the list, just restart the VS and check the menu. The same project/file has gone from the list

Note: Must restart VS

WS 2008 Service not start after system boot

I got problem when i deploy my software product to WS 2008 and its Windows service whose configuration was set to Automatic and was currently in running mode but after reboot the system it couldn't be started.

Solution: Go to Start->Run type services.msc select the service right click and select properties change Automatic to Automatic (Delayed Start) and press Ok
Next time you will see it in running mode


Configuration Information

• Automatic ~ With a service in this state, it will start at boot time. Some services, when no onger required, will also automatically stop when not needed. If you find you do not need a ervice, place it into Manual or Disabled.
• Automatic (Delayed Start) ~ With a service in this state, it will start just after boot time. Some services, when no longer required, will also automatically stop when not needed. If you find you do not need a service, place it into Manual or Disabled.
• Manual ~ Manual mode allows Windows to start a service when needed. However, very few services will start up when required in Manual mode. If you find you need a service, place it into Automatic.
• Disabled ~ This setting will stop a service from starting, even if needed. Errors in the Event Viewer will show up complaining of that fact. Some services, while Disabled, will constantly complain. However, this situation is taken care of if placed in Manual. The service descriptions identifies those that should be in Manual vs. Disabled.
• "Started" ~ A service that either set to Manual or Automatic that is running by default at or shortly after boot time.
• "Not Started" ~ A service set into Automatic that is not running by default at or shortly after boot time.

Reference: http://www.blackviper.com/WinVista/servicecfg.htm

Error 1920 Service faild to start WS 2003

Today i got a query from client that windows service for our product is not running it gave "Error 1920 service ... failed to start. verify that you have sufficient privileges to start system services" either the login user has the local Admin rights. I checked the Windows version it was Windows 2003 Sp2, i checked the event log and i found the event Id 7000, wow suspicious why it is not running under Admin control.
by googling i found it is a Microsoft documented bug that some times services wouldn't run even under Admin control. so following is the solution:

Just increase the timeout value in the registry, follow these steps:

1. Start Registry Editor (Regedit.exe).
2. To change the value data for the ServicesPipeTimeout DWORD value to 60000 in
the Control key, follow these steps:
1. Locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
2. Click the Control subkey
3. Right-click the ServicesPipeTimeout DWORD value, and then click Modify.
4. Click Decimal.
5. Type 60000, and then click OK.
3. If the ServicesPipeTimeout value is not available, add the new DWORD value,
and then set its value data to 60000 in the Control key. To do so, follow
these steps:
1. Locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet
2. Click the Control subkey.
3. On the Edit menu, point to New, and then click DWORD Value.
4. Type ServicesPipeTimeout, and then press ENTER.
5. Right-click the ServicesPipeTimeout DWORD value, and then click Modify.
6. Click Decimal.
7. Type a value of 60000, and then click OK.

The value is 60000 milliseconds and is equivalent to 60 seconds or to one minute.
Note This change does not take effect until the computer is restarted.

Reference: http://support.microsoft.com/kb/839803

Encryption and its types

Encryption

What is it and why is it necessary?

Goal of Encryption of Internet Traffic
conveys confidentiality to messages while in transit
changes readable text messages into something that cannot be read
discourages anyone from reading or copying the messages

Related Problem
if header information is not encrypted, traffic analysis is possible
traffic analysis - the analysis of header information in order to derive useful information from the headers

Encryption Components

• an algorithm
• a key

Encryption Algorithms
a series of steps that mathematically transforms plain-text or other readable information into unintelligible cipher text.
Cipher text - Data that has been encrypted. Cipher text is unreadable until it has been converted into plain text (decrypted) with a key.

Decryption
The inverse mathematical transformation, which transforms the encrypted cipher text back into something readable, is called decryption.

Encryption Algorithm - Input and Output
a key and plain text are input into an encryption algorithm
cipher text is output from the encryption algorithm

Encryption Keys
a bit string consisting of x number of bits. A 40 bit key is a string consisting of 40 bits
an encryption algorithm can use one of a large number of possible keys
the number of possible keys each algorithm can support depends on the number of bits in the key. The longer the key, the more the possible number of keys

Encryption Key Example
example - if the key length is 40, then 2 to the n, where n is the number of bits in the key, results in 1,000,000,000,000 possible key combinations, with each different key causing the algorithm to produce slightly d ifferent cipher output

Security and Encryption
encryption algorithms are considered secure if the security depends on only one factor - key length
security does not depend on secrecy, inaccessibility, or anything else, only on the key length
if this factor is true, then the only possible attack against the algorithm is a brute force attack

Brute Force Attacks and Security
all key combination must be tried in order to find the correct key
the length of the key determines the possible number of keys available for selection
the longer the key length the longer it takes to discover which key will actually decrypt
specifying a long enough key length makes a brute-force attack non-feasible

Symmetric Encryption
identical keys are used to encrypt and decrypt the message
a message encrypted by one specific symmetric key can only be decrypted by using the same key, it can be decrypted with a different key

Symmetric Keys
a random bit string, n bits long
most often generated on the source computer

Advantages of Using Symmetric Encryption
the encryption process is simple
each trading partner can use the same publicly known encryption algorithm - no need to develop and exchange secret algorithms
security is dependent on the length of the key

Drawbacks of Using Symmetric Encryption
a shared secret key must be agreed upon by both parties
if a user has n trading partners, then n secret keys must be maintained, one for each trading partner
authenticity of origin or receipt cannot be proved because the secret key is shared
management of the symmetric keys becomes problematic

Problems with Management of Symmetric Keys
trading partners must always use the exact same key to decrypt the encrypted message
key exchange is difficult because the exchange itself must be secure with no intervening compromise of the key
management of keys is difficult as numbers of trading partners increases, especially when multiple keys exist for each trading partner

Public Key Cryptography as a Solution for Managing Symmetric Keys
public key cryptography simplifies the management of symmetric keys to the point whereby a symmetric key can be used not only for each trading partner, but for each exchange between trading partners
additionally, public key cryptography can be used to unambiguously establish non-repudiation of origin and receipt

Asymmetric Encryption - (Public Key Cryptography)
based on the concept of a key pair
each half of the pair (one key) can encrypt information that only the other half (one key) can decrypt
the key pair is designated and associated to one, and only one, trading partner

Asymmetric Key Pairs
consists of two keys - one private and one public
private key is secret and only known by the designated trading partner it belongs to
public key is published widely but still associated only with the designated trading partner

Asymmetric Key Uses
confidentiality
digital signatures
both uses depend on the association of a key pair with one, and only one owner of the keys
both uses depend on one of the keys in the key pair being secret from everyone but the owner of the key

Confidentiality Using Asymmetric Key Pairs (Encryption)
Trading Partner A desires to send a confidential message to Trading Partner B
Trading Partner A retrieves Trading Partner B's public key and encrypts the message with it

Confidentiality Using Asymmetric Key Pairs (Decryption)
Trading Partner B receives the message and decrypts the message with the secretly held, private key
The only key that can possibly decrypt a message that is encrypted with Trading Partner B's public key is Trading Partner B's private key

Digital Signatures Using Asymmetric Key Pairs (Encryption)
Trading Partner A desires to send a digitally signed message to Trading Partner B
Trading Partner A uses their own private key to encrypt a part of the message
Trading Partner A sends the encrypted part of the message to B

Digital Signatures Using Asymmetric Key Pairs (Decryption)
Trading Partner B receives Trading Partner A's message and obtains A's public key
Trading Partner B tries to decrypt the encrypted portion of Trading Partner A's message
If it decrypts, Then Trading Partner B knows it has to be from A because the only thing A's public key will decrypt is something encrypted with A's private key and only A has access to that private key

Real World Usage of Asymmetric Encryption
public key encryption algorithms are considerably slower than symmetric key algorithms
rarely used as encryption methodology for bulk messages or parts of messages
normally used in conjunction with a Message Integrity Check (MIC) or to encrypt a symmetric key, where the MIC or symmetric key is what is encrypted using public key encryption algorithms

Speed Comparison - Symmetric vs Asymmetric
software encryption using DES (symmetric key algorithm) is 100 times faster than software encryption using RSA (asymmetric key algorithm) - estimate provided by RSA Data Securities
hardware encryption using DES (symmetric key algorithm) is anywhere from 1,000 to 10,000 times faster than hardware encryption using RSA (asymmetric key algorithm)

Encryption Needs for Confidential Commercial Exchanges
for interoperability between two trading partners
standard encryption algorithm(s)
standard key length(s)
agreed upon beforehand or within an individual transaction

Issues
how secure is the algorithm?
how fast are current implementations of the algorithm?
availability of APIs and/or tools to implement the algorithm
frequency of use of algorithm with other trading partners
sufficient key length to discourage brute force attacks

Common Symmetric Key Algorithms
Data Encryption Standard - DES
Triple DES
RC2 and RC5
IDEA

Block Ciphers vs Stream Ciphers
block ciphers - take a set number of bits, typically 64 bits, and encrypts the them as a single block
stream ciphers - take and encrypt one bit at a time
Most ciphers belong to the block cipher class.

Data Encryption Standard - DES
most widely used commercial encryption algorithm
in the public domain, available to all
a U. S. government encryption standard
security is known and is dependent solely on the key length
data sequenced into 64 bit blocks prior to encryption, each block encrypted

Cipher Block Chaining (CBC)
recommended mode for using DES
each 64 bit block of data is exclusively OR'd with the previous block before encryption
gives added protection by making each cipher-text block depend on each other
changes in the cipher text can be detected

Brute Force Attacks against DES
DES specifies a 56 bit key, so there are 2 to the 56th possible keys
brute force attack means trying every single key (10,000,000,000,000,000) to decrypt 8 bytes of known cipher text into the corresponding plain text

Resources Required to Break DES Key
$1 million dollar hardware based, brute-force attack on DES takes approximately 3.6 hours to recover the DES key
$1 million dollar software based, brute force attack on DES takes approximately 3 years to recover the DES key
above figures attributed to B. Schneier, "E-Mail Security", John Wiley & Sons, 1995

Triple DES
variant on DES which encrypts message 3 times with 2 independent 56 bit keys
effective key length is 112 bits
brute force attack on Triple DES is not feasible

RC2 and RC5
RSA owned proprietary symmetric key algorithms
variable key length makes security configurable
RC2 is a block cipher (similar to DES) and should be used in CBC mode, RC5 is also a block cipher and should be used in CVC Pad mode
Both use 128 bit key but support key masking for configuration of key length

International Data Encryption Algorithm (IDEA)
a block cipher, in the mold of DES
uses a 64-bit block size and a 128-bit key
IDEA in CBC mode is the bulk encryption algorithm used by Pretty Good Privacy (PGP) which makes it the most widely used encryption algorithm for

Key Lengths and Secure Transactions
Algorithms that make a brute force attack not feasible
Triple DES with 2 56 bit keys
RC2 and RC5 with 128 bit keys
IDEA with 128 bit key

Recommendations on Key Lengths
Transactions of minimal or small value - 40 bit RC2 or 56 bit DES
Most commercial applications need a key length of 75 bits
High value transactions Triple-DES, IDEA or 128 bit RC2 or RC5

Conclusions
Encryption is the correct method to implement confidentiality for Internet traffic
Symmetric key algorithms should be chosen for encryption of confidential data
The more bits in the symmetric key, the less probable the compromise of the encrypted data

Merge multiple dll into single one

Introduction

As you know, traditional linking of object code is no longer necessary in .NET. A .NET program will usually consist of multiple parts. A typical .NET application consists of an executable assembly, a few assemblies in the program directory, and a few assemblies in the global assembly cache. When the program is run, the runtime combines all these parts to a program. Linking at compile time is no longer necessary.

But sometimes, it is nevertheless useful to combine all parts a program needs to execute into a single assembly. For example, you might want to simplify the deployment of your application by combining the program, all required libraries, and all resources, into a single .exe file.

A single project

If all parts of your program are written by yourself in the same language, you can obviously just add all source files to a single project. The result will be a single DLL or EXE containing all dependencies.

csc /target:winexe /out:Program.exe 

      MainProgram.cs ClassLibrary1.cs ClassLibrary2.cs

However, if your program is written in multiple languages or if you are using binary third party libraries, you are out of luck.

.NET Modules

The .NET compilers already contain options for exactly this. If you compile a project, there is an option to create a module, which is similar to an assembly but without a manifest file. You can then use the al.exe tool to combine some of these modules to a single assembly. This feature makes it possible to create a single assembly that contains multiple languages.

First, you would compile the program and the class libraries to netmodules using the module target. Then you can use the assembly linker al.exe to combine these modules to a single assembly.

csc /target:module /out:ClassLibrary1.netmodule ClassLibrary1.cs

vbc /target:module /out:ClassLibrary2.netmodule ClassLibrary2.vb

vbc /target:module /out:Program.netmodule Program.vb

al /target:winexe /out:Program.exe ClassLibrary1.netmodule 

                 ClassLibrary2.netmodule Program.netmodule

But unfortunately, this method only works if you have all the required parts of your program either as source code or as .NET modules. If you are useing a third party class library in assembly form, you are again out of luck.

ILMerge

Since a .NET module is basically just an assembly without an assembly manifest, it should be possible to convert an assembly to a .NET module, at least that is what I thought. When researching this on Google, I found a tremendously useful tool on Microsoft research called ILMerge. This little gem makes it possible to link multiple assemblies to a single one.

First, you would compile your libraries to DLLs and your program to an EXE referencing the DLLs. This is exactly what Visual Studio would do if you had multiple libraries and a program referencing these libraries, so there is no need to do this on the command line.

csc /target:library /out:ClassLibrary1.dll ClassLibrary1.cs

vbc /target:library /out:ClassLibrary2.dll ClassLibrary2.vb

vbc /target:winexe /out:Program.exe 

    /reference:ClassLibrary1.dll,ClassLibrary2.dll Program.vb

This will produce a normal .exe that requires the two DLLs in the program directory or in the global assembly cache to run.

Now you can link these parts to a single self-contained EXE, using ILMerge:

ilmerge /target:winexe /out:SelfContainedProgram.exe 

        Program.exe ClassLibrary1.dll ClassLibrary2.dll

The nice thing about this is that you can also merge third party assemblies like commercial class libraries into your program. And you do not have to modify your build process. All you have to do is to merge the assemblies to a single EXE before deploying.

Conclusion

I found ILMerge tremendously useful, and I think that something like this should be a part of the .NET framework SDK. Maybe just enhance al.exe so that it can also link DLLs.

I have only scratched the surface of the .NET build process and the capabilities of ILMerge, and this article might contain many inaccuracies or even errors. But I found ilmerge.exe so useful that I just had to write about it.

Resources

• ILMerge: The ILMerge utility from Michael Barnett of Microsoft Research.