Saturday, July 18, 2009

SQL Tips : II (SHA 256 Encryption in SQL Server2000)

Heloo Every1,
This time it's encryption I dealt with. There are a lot of encryption Gurus and many forums full of algorithms. The only issue is none of them gave me a full implementation which is the most essential thing. Well I did implement the SHA 256 hashing algorithm in SQL server 2000 using extended stored procedure and in SQL server 2008 using assembly. I will explain you in detail what issues I did face and how did I overcome them.
First I will tell you how did I implement the hashing algorithm in SQL server 2000. I used the xp_hash_sha.dll which you can get from this link xp_hash_sha
Place this .dll file in you MSSQL/Binn folder. Now all you have to do is to register the .dll and create an extended stored procedure that can exploit the .dll file.
You can create a extended stored procedure using the following script.

USE [master]
SET NOCOUNT ON
GO
DECLARE @binn NVARCHAR(2000)
SELECT @binn = ''
CREATE TABLE #Temp(path NVARCHAR(2000))
INSERT INTO #Temp(path)
VALUES (@binn)
GO
IF EXISTS
(
SELECT *
FROM DBO.SYSOBJECTS
WHERE [ID] = OBJECT_ID('dbo.xp_hash_sha')
AND OBJECTPROPERTY(ID, N'IsExtendedProc') = 1
)
DROP PROCEDURE dbo.xp_hash_sha
GO
DECLARE @binn NVARCHAR(2000)
SELECT TOP 1 @binn = path + N'xp_hash_sha.dll'
FROM #Temp
EXEC dbo.sp_addextendedproc N'xp_hash_sha', @binn
GO



Now your extended stored procedure is ready. Now all you need to do is to create a function which can call the extended stored procedure.The procedure returns a varbinary and therefore the function must also have the same parameters.You can create the function using the below mentioned script

USE [master]
SET NOCOUNT ON
GO

IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE [ID] = OBJECT_ID(N'dbo.fn_hash_sha')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION dbo.fn_hash_sha
GO
CREATE FUNCTION dbo.fn_hash_sha (@plaintext VARBINARY(8000),
@bits INT)
RETURNS VARBINARY(64)
AS
BEGIN
DECLARE @hashcode VARBINARY(64)
EXEC dbo.xp_hash_sha @plaintext, @hashcode OUTPUT, @bits
RETURN @hashcode
END
GO



The problem it seems is solved but trust me here was the place where I was stuck. Actually the result is returned a varbinary where as the column of the table which I had to populate using this encryption was varchar. Now if I stored this result and tried to cast it to a varchar the output was really messy and totally undesired. To fix this I created one more function which did exactly what I wanted. Below mentioned script shows how to create the function.


IF EXISTS
(
SELECT *
FROM dbo.SYSOBJECTS
WHERE [ID] = OBJECT_ID(N'dbo.fn_plain_sha')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION dbo.fn_plain_sha
GO

create FUNCTION dbo.fn_plain_sha (@input VARchar(15))
RETURNS VARchar(75)
AS
BEGIN
DECLARE @t VARBINARY(75)
DECLARE @output VARchar(75)
set @t = master.dbo.fn_hash_sha (convert(varbinary,@input),256)
set @output= master.dbo.fn_varbintohexstr (@t)
RETURN upper (substring(@output,3,75))
END

GO



Here you see I hard coded 256 because I needed a sha 256. Also the first two characters '0X' was undesired so substring was taken. This functioned was used by me explicitly while populating the encrypted column in the required table.

Hope this documentation makes sense and helps a lot of people. Will update the process I used in SQL server 2008 shortly.

Note: The xp_hash_sha.dll is created by Michael Coles. You can also download the whole script for instatllation and unistallation of the .dll SHA.zip



References: http://www.sqlservercentral.com/articles/Administration/sql2000dbatoolkitpart4/2364/

Friday, July 17, 2009

SQL Tips : I

Image1



Image2





Heloo Evry1,
It's been sometime now I have been working on SQL Server and found somethings that are odd and some rely cool things about it. Though there is still an infinite left to learn I'm sure, I have some tips and tricks which were a hellava use to me. So thought of jotting them down.
Lets start with some thing very small and usefull. Many of you may have observed that the sql server 2000 is buggy in showing the dependency of the objects.
It is very irksome in SQL2000 Query Analyzer to find the dependency of the tables on different objects. Reason being most of the time the result is incomplete.
It’s just a matter of few moments now and after that you will be able to find dependencies of any kind of object be it a stored procedure, table or sql jobs.
NOTE: these queries are only limited to sql server 2000
We will utilize few systables for that purpose syscomments , sysobjects , sysjobs and sysjobsteps. You will need DBA rights to access these tables.



First let me show you how a dependency of a table is shown in the Query Analyzer in SQL 2000 GUI. Here we find the dependency of the table tv_family as shown in Image1.
Here 5 dependencies are shown which is incomplete. Now we will use the systables to find the actual dependencies of the table.
The Image2 mentions the query and the correct dependency.Here we see there are 161 objects of different types dependent on the table where as the GUI showed only 5.
Now let me give you a small brief as to how this simple query does this magic.
The systable sysobjects contains all the kind of objects created in the database concerned. They have a unique id now syscomments consist of text field which contains the whole script in case of a Stored procedure, Function, etc.. Now as the query suggests we essentially hunt for the text ‘tv_family’ in the join and extract the names of all the sysobjects and then order by as per our needs.

The above shown is a very generic query to find any object using a like query so you may get additional objects names than the name you are searching for.

Note: to be continued...

Friday, July 10, 2009

Chrome OS a causal Effect of Bing???



Look @ Future Of Desktops

 
So here is Google with it's Chrome OS as a more of Web User friendly OS. An expected but too quick a response to MS Bing. Or may I interpret it the other way round. Did MS knew about the OS launch and advance the release Bing. Well MS has done that a lot times nothing new about it. Whatever be the reason I'm really looking forward for the Chrome OS. Though I know it will be long before people accept changes, but we should not forget that before Google came up with the Search Engine none thought of it as a good field to invest resource and money. Thank God that Larry and Sergey didn't think of it this way. Where would we be without Google as we are today.
 
So now lets have a deep look what is Google promising in the new OS. Well it is a OS restructured around Linux and is for the people who spend most of their computing time on Web. It may be a hidden indication that much of development work which is based on MS platform will not be available and is quite understood. Well the Snapshots on the Header gives a look which not very far from present flavours of Linux available. The tough battle is not to give an amalgamation of existing OS but to make it run on the existing hardwares. Existing Linuxes available are still not able to run the desktops with existing hardwares. Trust me there are sleepless nights between installing and working on a Linux OS. Some of the variations have made commendable progress but still the job is not done as neatly as MS.
 
To be frank MS is prevalent only in developing countries, where civilians are computer literate in the manner that they how to switch it on and off and write a few docs and play games or surf on net. People get agitated when they bring a new hardware attach it to their comp and it doesn't get automatically installed. People don't have the literacy to install a driver on their own. They call up the support guy only to get a reply that they do not make drivers for open source. So people stick to MS where all they have to do is attach a device and start using it instantly. This has not been possible for MS also in a day it's been couple of decades and everyday a new patch is there to install. Bottom line is Google has to struggle a lot to impress people to use it's OS. But trust me proprietary race in the field of Software started by MicroSoft is going to end sooner or Later.
 
Well lets just wait and watch who wins the battle but 1 thing is for sure whatever happens it's the end user who will reap the benefit of a better OS. Keep Googling!!!

http://www.thinkgos.com/gos/download.htm