Hi there,

I have a custom DTS task that import an ETL file into a SQL database. Within the DTS package, I used the package object to retrieve the SQL connection, from which I retrieved the userID and password specified for SQL Authentication. I am getting a 'null' value for the password property. Is this a known issue? How do I work around it?

Thanks!

In message <35874FAF-309C-4A9A-A8F2-2EB81D9E13B9 (AT) microsoft (DOT) com>, cc
<cc (AT) discussions (DOT) microsoft.com> writes
The password on a DTS connection object was made write-only some time
ago, somewhere around SQL 7.0 SP 2, +/- 1 SP, I forget which SP exactly
introduced that change. All in the name of security.

Workaround is to find another storage location for the (connection
details and) password. One I quite like is to the SQL-DMO registered
servers collection. Just setup the registrations in EM, and read them
off as required.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Hi Darren,

Thanks for confirming it!
I'm not familiar with SQL-DMO, but doesn't that just retrieves the available servers? Is the password that the user specified via the Connection (e.g. Microsoft OLE DB Provider for SQL Server...) property dialog be available too?

I would like to be able to maintain the legacy behavior of our custom DTS task, which is to have a connection set up before allowing the configuration of the custom task. During execution of the task, we retrieve the userID and password (if using SQL Authentication) to connect to the SQL database.

Would appreciate any guidance on this. Thanks!

"Darren Green" wrote:

DMO Registered servers is distinct from DTS. It shows the lists of servers
registered in Enterprise Manager, but if set correctly it also has username
and passwords available. So it is a potential central storage location for
this information.

Any tasks that use connections now will need to change to use a new storage
location for this information, registered servers is just one option for
this location.


--
Darren Green
http://www.sqldts.com

"cc" <cc (AT) discussions (DOT) microsoft.com> wrote

Connection (e.g. Microsoft OLE DB Provider for SQL Server...) property
dialog be available too?
of the custom task. During execution of the task, we retrieve the userID and
password (if using SQL Authentication) to connect to the SQL database.

Option Explicit

Main

Function Main()

Dim oApplication ' SQLDMO.Application
Dim oServerGroups ' SQLDMO.ServerGroups

Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups

EnumerateServers oServerGroups
End Function

Function EnumerateServers(ByVal oServerGroups)
Dim oServerGroup ' SQLDMO.ServerGroup
Dim oRegisteredServer ' SQLDMO.RegisteredServer

For Each oServerGroup In oServerGroups
For Each oRegisteredServer In oServerGroup.RegisteredServers
MsgBox oRegisteredServer.Name
' Read off server properties, including username and password here!!!
Next
EnumerateServers oServerGroup.ServerGroups
Next
End Function



"cc" <cc (AT) discussions (DOT) microsoft.com> wrote

under the servers?