Hide password in connection string ?
-
Any suggestions on how to hide the password in the OLE DB connection string when using VBScript in a DTS? Or use a different method to accomplish the following?
(The DTS loops through the records in a table to search for filenames in a text file. If all files are found, continue on in the DTS.)
Example of code and why needed:
Function Main()
' Cursor Type constants
Const adOpenForwardOnly = 0
Const adOpenKeySet = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
' Lock Type constants
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
' Options constants
Const adCmdText = 1
Const adCmdTable = 2
Const adCmdStoredProc = 4
Const adCmdUnknown = 8
Const adCmdTableDirect = 512
Dim sRESULTS_FILE, sSEARCH_FILE
sRESULTS_FILE = "d:\Ftp\ftp_results.txt"
Dim cn, rs, sSQL
Dim filesys, readfile, contents
Dim filecount, foundfile
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FileExists(sRESULTS_FILE) Then
'Initialize counters'
filecount = 0
foundfile = 0
' Create the Connection and RecordSet objects
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' Set connection string and open it
cn.ConnectionString = "driver={SQL Server};server=myserver;uid=myuserid;pwd=mypassword;database=mydatabase"
cn.Open
' Set the command text for the RecordSet
sSQL = "SELECT ftp_filename + '.' + file_extension AS SearchFile FROM pwz_ftp_file WHERE ftp_cmd = 'get'"
' Open the recordset
With rs
.Open sSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
' If we have data
Do While Not .EOF
sSEARCH_FILE = .Fields("SearchFile").Value
filecount = filecount + 1
Set readfile = filesys.OpenTextFile(sRESULTS_FILE, 1, false)
Do While readfile.AtEndOfStream <> true
contents = readfile.ReadLine
If InStr(contents, "13 " & sSEARCH_FILE) Then
foundfile = foundfile + 1
End If
Loop
readfile.close
.MoveNext
Loop
End With
' If number of files found = number of files required
If foundfile = filecount Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
' Clean up
rs.Close
cn.Close
Set rs = nothing
Set cn = nothing
Else
Main = DTSTaskExecResult_Failure
End If
End Function
-
Best you can do is obscure it, not sure even that worth doing. Figure anyone who can edit the package can see the password one way or another. Could look at trying to use NT authentication instead.
Andy
-
The best method is to use NT Authentication then there is no userid or password to worry about.
I set my ADO Connection string up as a Global Variable. The userid and password are passed in as parameters on Package execution. When it completes, the Global Variable(s) are cleared.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
Thanks for the responses. Before I tackle the NT method, is there a way for a SQL Task (version 7.0) to evaluate a return value from a stored procedure and then instruct the DTS to succeed or fail.
In other words, SQL Task code would look something like this:
------------------------------
declare @return_status int
exec @return_status = stp_MonthlyExtractExecution
if @return_status = 1
begin
-- FAILURE: GO TO NEXT 'ON FAILURE' TASK
-- something like VBScript: Main = DTSTaskExecResult_Failure
end
else
begin
-- SUCCESS: GO TO NEXT 'ON SUCCESS' TASK
-- something like VBScript: Main = DTSTaskExecResult_Success
end
------------------------------
-
use RAISERROR in the SP should do it
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
I created SPROC:
create procedure dbo.stp_test
as
if GetDate() = GetDate()
begin
RAISERROR ('DTSTaskExecResult_Success', 16, 1)
end
When I ran it via SQL Query Analyzer, I get results:
Server: Msg 50000, Level 16, State 1, Procedure stp_test, Line 6
DTSTaskExecResult_Success
So far, so good.
When I run it via a SQL Task in DTS with code:
exec stp_test
The task fails during execution
What am I doing wrong?
-
You need to use the RETURN statement, not RAISERROR.
eg: Return(1)
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface -
Phil is right, the thing with RAISERROR thou is so you can send your own custom message back which I prefer as it will be logged with your message. But with RAISERROR you must place a RETURN right after it.
Ex.
RAISERROR('This did not go as planned, halt.', 11, 1)
RETURN
which you really only need to do this when it is a failure you want to create, success should be done simply by doing
RETURN
at the point you want to jump out of the code.
Now if you choose not to use RAISERROR the your return should look like this to be a failure, then you must use RETURN(anonzerovalue) to indicate this. RETURN by itself is understood RETURN(0).
See RAISERROR and RETURN in BOL for more details.
-
Let's say that a SQL Task in a DTS calls the stored procedure which 'RETURN's a value. What is the SQL syntax to get the DTS to interpret that value as a success (go to the 'on success' task), or failure (go to the 'on failure' task)?
-
Try this and I think you will have some luck.
Create a simple procedure:
CREATE PROCEDURE p_test_success
@success_cd TINYINT = 0
AS
begin
-- @success_cd = 1 = failure
-- @success_cd = 0 = success
IF @success_cd != 0 RAISERROR 99999 "Failure"
RETURN @success_cd
end
go
GRANT EXECUTE ON p_test_success TO PUBLIC
go
Now create a Execute SQL Task which runs this proc.
Now create a "Success" e-mail and hook it via a On Success workflow.
Now create a "Failed" e-mail and hook it via a On Failure workflow.
Execute the package and pass either a 1 to test for a failure or a 0 to test for a success.
Jeff
-
It is possible to store the connection string into the Windows registry.
To extract the value from the registry using VBScript:
Dim strConn, ShellObject
Set ShellObject = WScript.CreateObject("WScript.Shell")
strConn = ShellObject.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath")
Set ShellObject = Nothing
It's also possible to use xp_regread to get the value in TSQL:
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLPath'
This stored procedure accepts three parameters:
root key,
path to the key,
key value you are looking to return.
Note: allowing access to the registry on the server has it's own risks.
-
Bingo !!! The key I was missing was the ol' "RAISERROR 99999 'Failure'". Doesn't take much to stop a horse, does it? Also, thanks for the alternative -- the registry option.
Thanks to all again.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply