January 14, 2009 at 1:08 am
Dear all,
I am ask by the management to change all password in our system regularly, this includes DTS password,
Do any of you know how to mass change DTS password in a single DB?
If it could be applied for both SQL 2000 and SQL 2005, it would be great.
thx
hansen
January 14, 2009 at 1:32 am
Few years ago I wrote a script with vbscript that opened the packages then looped through the package’s object and for every connection object that was found, it checked if for the user’s name and then modified the password for a certain user. I don’t have this script now, but if you’ll search the internet, you’ll be able to find information about the dts package object model and you could write a script on your own in couple of hours. How ever I don’t think that it will also work for SQL Server 2005 and SSIS.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2009 at 12:25 pm
Hansen,
Are you talking about login passwords in connections or owner passwords on packages? It sounds like Adi's vbscript is a good option for changing connections, but I don't know of any way to change owner passwords on multiple packages without opening each one.
Greg
January 14, 2009 at 7:06 pm
Well, What we have is DTS(s) that are scheduled to on different time everyday. These DTS(s) uses password for their security to preventing anybody from editing it.
Since my company are now using a lot of outsourcing IT, Management feel that changing those password regularly is a must even if there are no changes.
I am looking at Adi's suggestion at the moment and trying a code the script. hopefully it would be a success.
thx guys.
hansen
April 22, 2010 at 3:06 am
Could you please shar that script, I am in need of it.
Thanks,
MJ
April 22, 2010 at 3:30 am
MANU-J. (4/22/2010)
Could you please shar that script, I am in need of it.Thanks,
MJ
I once did an SSIS package to add lines to 300 database scripts. With some modification, this'd work for what you're talking about.
Public Sub Main()
Dim FILE_NAME As String
Dim i As Integer
Dim aryText(3) As String
Dim strSearch As String
FILE_NAME = Dts.Variables("SourceFile").Value.ToString
'Passes filename to "FILE_NAME"
aryText(0) = ", [StartDate] [datetime] null"
aryText(1) = ", [EndDate] [datetime] null"
aryText(2) = ", [Current Flag] [char](1) null"
aryText(3) = ", [UID] [bigint] Identity"
'Lines to be appended into each SQL Query
strSearch = ") ON [PRIMARY]"
'String to search for
IO.File.Copy(FILE_NAME, FILE_NAME & ".bak")
'Backup source
Dim objReader As New System.IO.StreamReader(FILE_NAME & ".bak")
Dim objWriter As New System.IO.StreamWriter(FILE_NAME)
objWriter.AutoFlush = True
'Open "FILE_NAME" for writing
Dim strTemp As String
Do Until objReader.EndOfStream
strTemp = objReader.ReadLine
If strTemp.Contains(strSearch) Then
'Where the character is found in the file, begin the for loop
'To add the new data.
For i = 0 To 3
objWriter.WriteLine(aryText(i))
'Append the four text lines
Next
End If
objWriter.WriteLine(strTemp)
Loop
objReader.Close()
objWriter.Close()
IO.File.Delete(FILE_NAME & ".bak")
End Sub
April 23, 2010 at 7:46 am
How is the password stored? I've got a few scripts that loop through and change things like global variables I may have something you can work from.
April 26, 2010 at 8:45 am
Passwords are just stored in global variables in dts packages.
Thanks in advance,
Manu
April 26, 2010 at 9:44 am
I pieced together from code examples found online. I would give credit if I knew where to give it, but as it is this was something I kludged together to handling changing >500 DTS packages on a one day notice that we would need to change our production passwords.
[font="System"]:exclamation: Warning for those that need that kind of thing. Please review the code and test it in a safe environment before using.[/font]
main()
Function Main()
Dim oApplication ' As DTS.Application
Dim oPackageSQLServer ' As DTS.PackageSQLServer
Dim oPackageInfos ' As DTS.PackageInfos
Dim oPackageInfo ' As DTS.PackageInfo
Dim oPackage ' As DTS.Package
Dim oGlobal ' As DTS.GlobalVariable
Dim gvArray' Array of local variables
Dim strOld1, strOld2, strOld3, strNew1, strNew2, strNew3, strSQLsaPass, strServerName
strOld1 = ""' <--- String to be replaced
strNew1 = ""' <--- New string
strSQLsaPass = ""' <-- Set sa password
strServerName = Wscript.Arguments(0)
Wscript.Echo "Starting..."
Set oApplication = CreateObject("DTS.Application")
Set oPackageSQLServer = oApplication.GetPackageSQLServer(strServerName,"sa",strSQLsaPass,0)
Set oPackageInfos = oPackageSQLServer.EnumPackageInfos("", True, "")
Set oPackageInfo = oPackageInfos.Next
set gvArray = CreateObject("System.Collections.ArrayList")
'Note: It is IMPORTANT that oPackage be instantiated and destroyed within the loop. Otherwise,
'previous package info will be carried over and snowballed into a bigger package every time
'this loop is run. That is NOT what you want.
Do Until oPackageInfos.EOF
Set oPackage = CreateObject("DTS.Package2")
oPackage.LoadFromSQLServer strServerName, "sa", strSQLsaPass,DTSSQLStgFlag_Default , , , , oPackageInfo.Name
Wscript.Echo CrLf
Wscript.Echo oPackageInfo.Name
For Each listItem In oPackage.GlobalVariables
strText = ""
strNewText = ""
set oGlobal = Nothing
set oGlobal = listItem
strText = oGlobal.value
strNewText = Replace(strText, strOld1, strNew1)
If strText <> strNewText1 Then
oGlobal.Value=strNewText1
gvArray.add(oGlobal)
Wscript.Echo listItem.name & " - Modified" & CrLf
Else
gvArray.add(listItem)
Wscript.Echo listItem.name & CrLf
End If
Next
For Each Item in gvArray
oPackage.GlobalVariables.Remove(Item.Name)
oPackage.GlobalVariables.Add Item
Next
oPackage.SaveToSQLServer strServerName, "sa", strSQLsaPass, DTSSQLStgFlag_Default
gvArray.clear()
set oPackage = Nothing
set oPackageInfo = oPackageInfos.Next
Loop
'Clean up and free resources
Set oApplication = Nothing
Set oPackageSQLServer = Nothing
Set oPackageInfos = Nothing
Set oPackageInfo = Nothing
Set oPackage = Nothing
Set oGlobal = Nothing
Main = DTSTaskExecResult_Success
End Function
April 27, 2010 at 3:22 am
Thanks for sharing the code.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply