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

    http://www.sqlservercentral.com/columnists/awarren/

  • 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