Mega Code Archive
MS SQL Extended Stored Procedures Tutorial (Part 2)
<![CDATA[
Title: MS SQL Extended Stored Procedures Tutorial (Part 2)
Question: Based on the API inteface (MsOdsApi) in Part 1 we can now build a working DLL that we can call as a SQL statement. The example uses my LogonUserSSPI() function that can be found in article
"LogonUser() Win API call vs SSPI call"
http://www.delphi3000.com/articles/article_3436.asp
If you don't want to use LogonUserSSPI() for the demo then feel free to set the cResult variable to 'Y' or 'N' any way you choose. You can then drop the ValLogonW2000 from the uses clause.
Once compiled copy the DLL to the equivalent directory ....
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
Note : In the case of SQL Server 2005 \80\ will be \90\
From SQL Analyzer in master database issue command
sp_addextendedproc 'xp_ValidateUser','xp_ValidateSSPI.dll'
If you now issue command ...
exec master..xp_ValidateUser 'someuser',somedomain','somepass'
You should see a result set consisting of 1 row and 1 column called 'Valid' with either 'Y' or 'N' in it.
Delphi Usage Example.
...
Query1.SQL.Text := 'exec master..xp_ValidateUser ''koos'',''xdomain'',''mypassw''';
Query1.Open;
if Query1.Fields[0].AsString = 'Y' then
MessageBox('User is Valid')
else
MessageBox('User is NOT Valid');
Query1.Close;
...
Note : Don't forget they stay in memory once run. So if you need to recopy the DLL after modification use the following events ...
use master
go
sp_addextendedproc 'xp_ValidateUser','xp_ValidateSSPI.dll'
go
exec master..xp_ValidateUser 'mheydon','pgbison','xxxxxxx'
go
sp_dropextendedproc 'xp_ValidateUser'
go
dbcc xp_ValidateSSPI(free)
go
As you can see the API is rather unwieldy.
Next .. Create a Class that hides the API's complexity
Answer:
library xp_ValidateSSPI;
// =============================================================================
// MS SQL Extended Stored Procedure XP_VALIDATEUSER
//
// Validate User Credentials using SSPI
// exec xp_ValidateUser 'user','domain','password'
//
// Mike Heydon 2006
//
// Place compiled DLL in Microsoft SQL Server\80\Tools\Binn directory.
// or ../90/.. for SQL 2005 etc.
// SQL Commands to test procedure
//
// use master
// sp_addextendedproc 'xp_ValidateUser','xp_ValidateSSPI.dll'
// exec master..xp_ValidateUser 'mheydon','pgbison','xxxxxxx'
// sp_dropextendedproc 'xp_ValidateUser'
// dbcc xp_ValidateSSPI(free)
//
// See "LogonUser() Win API call vs SSPI call"
// http://www.delphi3000.com/articles/article_3436.asp
// =============================================================================
uses SysUtils,Windows,Classes,ValLogonW2000,MsOdsApi;
{$R *.res}
const
XP_NOERROR = 0;
XP_ERROR = 1;
// ==================================================================
// It is highly recommended that all Microsoft SQL Server (7.0
// and greater) extended stored procedure DLLs implement
// and export __GetXpVersion.
// ==================================================================
function _GetXpVersion : longword; cdecl;
begin
Result := ODS_VERSION;
end;
// ===================================================================
// Display usage message
// usage: exec xp_ValidateLogon
// ===================================================================
procedure _PrintUsage(ASrvProc : SRV_PROC);
begin
srv_SendMsg(ASrvProc,SRV_MSG_ERROR,20001,SRV_INFO,1,
nil,0,0,
'Usage: exec xp_ValidateLogon ,,',
SRV_NULLTERM);
srv_SendDone(ASrvProc,(SRV_DONE_ERROR or SRV_DONE_MORE),0,0);
end;
// ===================================================================
// Display error message
// ===================================================================
procedure _PrintError(ASrvProc : SRV_PROC; AErrorMsg : PChar);
begin
srv_SendMsg(ASrvProc,SRV_MSG_ERROR,20001,SRV_INFO,1,
nil,0,0,AErrorMsg,SRV_NULLTERM);
srv_SendDone(ASrvProc,(SRV_DONE_ERROR or SRV_DONE_MORE),0,0);
end;
// ===================================================================
// Main MS SQL Function Entry Point
// ===================================================================
function xp_ValidateUser(ASrvProc : SRV_PROC) : SRVRETCODE; cdecl;
var iType : byte;
i,iMaxLen,
iActualLen,iErrCode : integer;
bNull : longbool;
cResult : char;
pData : pointer;
sData,sLogon,
sDomain,sPassword : string;
begin
// Count number of input parameters.
// There should 3 ie. ALogonCode,ADomain,APassword
if (srv_rpcparams(ASrvProc) 3) then begin
_PrintUsage(ASrvProc);
Result := XP_ERROR;
exit;
end;
// Check all 3 are of type string
for i := 1 to 3 do begin
// Get parameter type and length information.
if (srv_ParamInfo(ASrvProc,i,@iType,@iMaxLen,@iActualLen,
nil,@bNull) = FAIL) then begin
_PrintError(ASrvProc,'srv_paraminfo failed...');
Result := XP_ERROR;
exit;
end;
// Make sure parameter is of char or varchar datatype (string)
if (iType SRVBIGVARCHAR) and (iType SRVBIGCHAR) then begin
_PrintUsage(ASrvProc);
Result := XP_ERROR;
exit;
end;
// Create Delphi string from pointer
pData := srv_ParamData(ASrvProc,i);
SetLength(sData,iActualLen);
move(pData^,sData[1],iActualLen);
case i of
1 : sLogon := sData;
2 : sDomain := sData;
3 : sPassword := sData;
end;
sData := '';
end;
// Call the SSPI Validation Check
// You can substitute your own Y/N funtion here for demo purposes
// if you don't want ti use LogonUserSSPI
if LogonUserSSPI(sLogon,sDomain,sPassword,iErrCode) then
cResult := 'Y'
else
cResult := 'N';
// Describe the results set
srv_Describe(ASrvProc,1,'Valid',SRV_NULLTERM,SRVBIGCHAR,1,SRVBIGCHAR,
1,@cResult);
// Send a row to client
if (srv_SendRow(ASrvProc) = FAIL) then begin
_PrintError(ASrvProc,'srv_sendrow failed...');
Result := XP_ERROR;
exit;
end;
srv_SendDone(ASrvProc,(SRV_DONE_COUNT or SRV_DONE_MORE),0,1);
Result := XP_NOERROR;
end;
// =============================================================================
// Export declarations
// =============================================================================
exports xp_ValidateUser,
_GetXpVersion;
begin
end.
]]>