Data Source passwords in Config Files

Anybody find a sound approach to being able to store DataSource connection information in a configuration and be able to access a Data Source that requires a password for login?

Storing it as a configuraiton in the DB is not an option, as you require the pw to access the DB that would store it.

Can security be applied to an environment variable? Registry?

Anyone successfully use the registry to store configuration values?I'm not sure what your criterion for sound is, so I don't know if this meets it :), but you can store credentials (username & password) in a configuration file, and then make an environment variable which gives its path, and then configure a Package Configuration in the SSIS package to use the environment variable.
Of course you have to configure the environment variable for each machine (and for each user, if two users on one machine will use different credentials -- therefore, different configuration files).
Then, the credentials being now in one or more files, secure access to these files via traditional NTFS ACLs (file pemissions).
|||

I guess by sound, I meant, any way that works... when i store the pw in the config the .xml file represents it like the following:

- <Configuration ConfiguredType="Property" Path="\Package.Connections [MDB].Properties[Password]" ValueType="String">

<ConfiguredValue />

</Configuration>

This results in the error:
Error 1 Validation error. Data Flow Task: OLE DB Source [1]: The AcquireConnection method call to the connection manager "MDB" failed with error code 0xC0202009. Package2.dtsx 0 0

Have you been able to do this?.. am I missing something? I am also storing the connection string, initial catalog, and username in the config file.

|||When the file is generated by Visual Studio, it will not write the pw. I was able to get it to work by modifying the file directly.. duh.|||I'm assuming that you're not on a domain and so cannot use integrated authentication?
K|||Yes, Joe, We've been using the solution I explained above, with different configurations for different developers on the same machine (so they can use different login names, so the processes are distinguishable in SQL Server Enterprise Manager).
But, we've been using SQL Server logons (not integrated), because we're using a SQL Server 2000 backend. What you quoted sounds like you're using an mdb backend, which we've not tried.
To set up a new configuration with a new set of credentials, I first add the environment variable for that user, pointing it to where the config file is going to be. Then I put a config file there -- I take an existing one, and using a text editor, change the logon name & password (stored in plaintext in the file).
The very first time we did this, SSIS generated the config file for us; since then, we've been hand editing it for each machine and/or credential combination.

|||Here is an edited copy of our config file. Everything beginning with Zz is something that I edited before posting (along with the nifty DEADBEEF guid).
Also I added the carriage returns and whitespaces -- the raw config file is a whitespaceless mess.
******************

<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="ZzMachineName\ZzUserName" GeneratedFromPackageName="Zz Our Nifty Package" GeneratedFromPackageID="{DEADBEEF-AAAA-BBBB-CCCC-DEADBEEFBEEF}" GeneratedDate="7/26/2005 4:25:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_CONN_NAME].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[Password]" ValueType="String">
<ConfiguredValue>ZzBigSecretPassword</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[ZZ_OUR_NOTHER_CONN].Properties[UserName]" ValueType="String">
<ConfiguredValue>ZzLogonNameForThisDeveloperHere</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Data Source on another server

I have always used Reporting Services to point to a DB on the same server as
RS.
If I set the datasource to another server I get:
Reporting Services Error
----
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'dbAudit'.
(rsErrorOpeningConnection) Get Online Help
a.. Login failed for user 'Bha'. Reason: Not associated with a trusted
SQL Server connection.
----
How do I set this as a trusted connection?My guess is a problem with how you have set up your credentials for the data
source.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> I have always used Reporting Services to point to a DB on the same server
as
> RS.
> If I set the datasource to another server I get:
> Reporting Services Error
> ----
--
> a.. An error has occurred during report processing.
(rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'dbAudit'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Login failed for user 'Bha'. Reason: Not associated with a
trusted
> SQL Server connection.
> ----
--
>
> How do I set this as a trusted connection?
>|||Its set as Windows Auth...Any special access I need on the DB at the other
server?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> My guess is a problem with how you have set up your credentials for the
> data
> source.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> I have always used Reporting Services to point to a DB on the same server
> as
>> RS.
>> If I set the datasource to another server I get:
>> Reporting Services Error
>> ----
> --
>> a.. An error has occurred during report processing.
> (rsProcessingAborted)
>> Get Online Help
>> a.. Cannot create a connection to data source 'dbAudit'.
>> (rsErrorOpeningConnection) Get Online Help
>> a.. Login failed for user 'Bha'. Reason: Not associated with a
> trusted
>> SQL Server connection.
>> ----
> --
>>
>> How do I set this as a trusted connection?
>>
>|||When you setup a datasource there is a tab that says credentials. You are
telling RS how to access the data for the report. For instance, I go against
Sybase a lot. I set it to a special username and password that is a readonly
username. Although you can tell it to use the username and password of the
person running the report, I recommend against doing that. If you can use a
set username and password then the system will pool connections and it can
reuse a connection rather than create a new one for each user.
Read up on credentials in the help.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
> Its set as Windows Auth...Any special access I need on the DB at the other
> server?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> > My guess is a problem with how you have set up your credentials for the
> > data
> > source.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> >> I have always used Reporting Services to point to a DB on the same
server
> > as
> >> RS.
> >>
> >> If I set the datasource to another server I get:
> >>
> >> Reporting Services Error
>> ----
-
> > --
> >>
> >> a.. An error has occurred during report processing.
> > (rsProcessingAborted)
> >> Get Online Help
> >> a.. Cannot create a connection to data source 'dbAudit'.
> >> (rsErrorOpeningConnection) Get Online Help
> >> a.. Login failed for user 'Bha'. Reason: Not associated with a
> > trusted
> >> SQL Server connection.
> >>
>> ----
-
> > --
> >>
> >>
> >> How do I set this as a trusted connection?
> >>
> >>
> >
> >
>|||I see. I just assumed that the Windows Auth would work and use the access I
had provided to the db on that server.
John Hann
MOM MVP
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> When you setup a datasource there is a tab that says credentials. You are
> telling RS how to access the data for the report. For instance, I go
> against
> Sybase a lot. I set it to a special username and password that is a
> readonly
> username. Although you can tell it to use the username and password of the
> person running the report, I recommend against doing that. If you can use
> a
> set username and password then the system will pool connections and it can
> reuse a connection rather than create a new one for each user.
> Read up on credentials in the help.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> Its set as Windows Auth...Any special access I need on the DB at the
>> other
>> server?
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> > My guess is a problem with how you have set up your credentials for the
>> > data
>> > source.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> I have always used Reporting Services to point to a DB on the same
> server
>> > as
>> >> RS.
>> >>
>> >> If I set the datasource to another server I get:
>> >>
>> >> Reporting Services Error
>> ----
> -
>> > --
>> >>
>> >> a.. An error has occurred during report processing.
>> > (rsProcessingAborted)
>> >> Get Online Help
>> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> (rsErrorOpeningConnection) Get Online Help
>> >> a.. Login failed for user 'Bha'. Reason: Not associated with a
>> > trusted
>> >> SQL Server connection.
>> >>
>> ----
> -
>> > --
>> >>
>> >>
>> >> How do I set this as a trusted connection?
>> >>
>> >>
>> >
>> >
>>
>|||Hmm...Still getting the Not a trusted connection with the local user id to
the SQL server...
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>I see. I just assumed that the Windows Auth would work and use the access
>I had provided to the db on that server.
> John Hann
> MOM MVP
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> When you setup a datasource there is a tab that says credentials. You are
>> telling RS how to access the data for the report. For instance, I go
>> against
>> Sybase a lot. I set it to a special username and password that is a
>> readonly
>> username. Although you can tell it to use the username and password of
>> the
>> person running the report, I recommend against doing that. If you can use
>> a
>> set username and password then the system will pool connections and it
>> can
>> reuse a connection rather than create a new one for each user.
>> Read up on credentials in the help.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> Its set as Windows Auth...Any special access I need on the DB at the
>> other
>> server?
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> > My guess is a problem with how you have set up your credentials for
>> > the
>> > data
>> > source.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> I have always used Reporting Services to point to a DB on the same
>> server
>> > as
>> >> RS.
>> >>
>> >> If I set the datasource to another server I get:
>> >>
>> >> Reporting Services Error
>> ----
>> -
>> > --
>> >>
>> >> a.. An error has occurred during report processing.
>> > (rsProcessingAborted)
>> >> Get Online Help
>> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> (rsErrorOpeningConnection) Get Online Help
>> >> a.. Login failed for user 'Bha'. Reason: Not associated with a
>> > trusted
>> >> SQL Server connection.
>> >>
>> ----
>> -
>> > --
>> >>
>> >>
>> >> How do I set this as a trusted connection?
>> >>
>> >>
>> >
>> >
>>
>>
>|||Have you pulled up the datasource in Report Manager, properties and set the
credentials. You should see something like this:
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hmm...Still getting the Not a trusted connection with the local user id to
> the SQL server...
>
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> >I see. I just assumed that the Windows Auth would work and use the
access
> >I had provided to the db on that server.
> > John Hann
> > MOM MVP
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> When you setup a datasource there is a tab that says credentials. You
are
> >> telling RS how to access the data for the report. For instance, I go
> >> against
> >> Sybase a lot. I set it to a special username and password that is a
> >> readonly
> >> username. Although you can tell it to use the username and password of
> >> the
> >> person running the report, I recommend against doing that. If you can
use
> >> a
> >> set username and password then the system will pool connections and it
> >> can
> >> reuse a connection rather than create a new one for each user.
> >>
> >> Read up on credentials in the help.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
> >> Its set as Windows Auth...Any special access I need on the DB at the
> >> other
> >> server?
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> >> > My guess is a problem with how you have set up your credentials for
> >> > the
> >> > data
> >> > source.
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> >> >> I have always used Reporting Services to point to a DB on the same
> >> server
> >> > as
> >> >> RS.
> >> >>
> >> >> If I set the datasource to another server I get:
> >> >>
> >> >> Reporting Services Error
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >> a.. An error has occurred during report processing.
> >> > (rsProcessingAborted)
> >> >> Get Online Help
> >> >> a.. Cannot create a connection to data source 'dbAudit'.
> >> >> (rsErrorOpeningConnection) Get Online Help
> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
a
> >> > trusted
> >> >> SQL Server connection.
> >> >>
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >>
> >> >> How do I set this as a trusted connection?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Have you go into the datasource via Report Manager, properties? You should
see something like this for credentials:
Connect Using:
The credentials supplied by the user running the report
Display the following text to prompt user for a login name and
password:
Use as Windows credentials when connecting to the data
source
Credentials stored securely in the report server
To make sure everything is working just pick the first one. Then if you can
get to the database using query analyzer then report server can to. As I
mentioned before, I prefer specifying a single readonly user for the
credentials.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John Hann [MVP]" <blah@.blah.blah> wrote in message
news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hmm...Still getting the Not a trusted connection with the local user id to
> the SQL server...
>
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> >I see. I just assumed that the Windows Auth would work and use the
access
> >I had provided to the db on that server.
> > John Hann
> > MOM MVP
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> When you setup a datasource there is a tab that says credentials. You
are
> >> telling RS how to access the data for the report. For instance, I go
> >> against
> >> Sybase a lot. I set it to a special username and password that is a
> >> readonly
> >> username. Although you can tell it to use the username and password of
> >> the
> >> person running the report, I recommend against doing that. If you can
use
> >> a
> >> set username and password then the system will pool connections and it
> >> can
> >> reuse a connection rather than create a new one for each user.
> >>
> >> Read up on credentials in the help.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
> >> Its set as Windows Auth...Any special access I need on the DB at the
> >> other
> >> server?
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> >> > My guess is a problem with how you have set up your credentials for
> >> > the
> >> > data
> >> > source.
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
> >> >> I have always used Reporting Services to point to a DB on the same
> >> server
> >> > as
> >> >> RS.
> >> >>
> >> >> If I set the datasource to another server I get:
> >> >>
> >> >> Reporting Services Error
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >> a.. An error has occurred during report processing.
> >> > (rsProcessingAborted)
> >> >> Get Online Help
> >> >> a.. Cannot create a connection to data source 'dbAudit'.
> >> >> (rsErrorOpeningConnection) Get Online Help
> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
a
> >> > trusted
> >> >> SQL Server connection.
> >> >>
> >>
>> ----
--
> >> -
> >> > --
> >> >>
> >> >>
> >> >> How do I set this as a trusted connection?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>
begin 666 blank.gif
M1TE&.#EA`0`!`/<``````( ```" `(" ````@.( `@. " @.,# P,#<P-30R$ @.
M`& @.`( @.`* @.`, @.`. @.``! `"! `$! `&! `(! `*! `,! `.! ``!@.`"!@.
M`$!@.`&!@.`(!@.`*!@.`,!@.`.!@.``" `"" `$" `&" `(" `*" `," `." ``"@.
M`""@.`$"@.`&"@.`("@.`*"@.`,"@.`."@.``# `"# `$# `&# `(# `*# `,# `.#
M``#@.`"#@.`$#@.`&#@.`(#@.`*#@.`,#@.`.#@.````0" `0$ `0& `0( `0* `0, `
M0. `0 `@.0" @.0$ @.0& @.0( @.0* @.0, @.0. @.0 ! 0"! 0$! 0&! 0(! 0*!
M0,! 0.! 0 !@.0"!@.0$!@.0&!@.0(!@.0*!@.0,!@.0.!@.0 " 0"" 0$" 0&" 0("
M0*" 0," 0." 0 "@.0""@.0$"@.0&"@.0("@.0*"@.0,"@.0."@.0 # 0"# 0$# 0&#
M0(# 0*# 0,# 0.# 0 #@.0"#@.0$#@.0&#@.0(#@.0*#@.0,#@.0.#@.0 ``@." `@.$ `
M@.& `@.( `@.* `@., `@.. `@. `@.@." @.@.$ @.@.& @.@.( @.@.* @.@., @.@.. @.@. ! @."!
M@.$! @.&! @.(! @.*! @.,! @..! @. !@.@."!@.@.$!@.@.&!@.@.(!@.@.*!@.@.,!@.@..!@.@. "
M@."" @.$" @.&" @.(" @.*" @.," @.." @. "@.@.""@.@.$"@.@.&"@.@.("@.@.*"@.@.,"@.@.."@.
M@. # @."# @.$# @.&# @.(# @.*# @.,# @..# @. #@.@."#@.@.$#@.@.&#@.@.(#@.@.*#@.@.,#@.
M@..#@.@. ``P" `P$ `P& `P( `P* `P, `P. `P `@.P" @.P$ @.P& @.P( @.P* @.
MP, @.P. @.P ! P"! P$! P&! P(! P*! P,! P.! P !@.P"!@.P$!@.P&!@.P(!@.
MP*!@.P,!@.P.!@.P " P"" P$" P&" P(" P*" P," P." P "@.P""@.P$"@.P&"@.
MP("@.P*"@.P,"@.P."@.P # P"# P$# P&# P(# P*# P/_[\#INI8" @./\```#_
J`/__````__\`_P#______R'Y! $``/T`+ `````!``$```@.$`/L%! `[
`
end|||I did...May call this in to PSS...Don't want to waste your time
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ec25o4ZzEHA.3820@.TK2MSFTNGP11.phx.gbl...
> Have you pulled up the datasource in Report Manager, properties and set
> the
> credentials. You should see something like this:
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
>> Hmm...Still getting the Not a trusted connection with the local user id
>> to
>> the SQL server...
>>
>> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>> >I see. I just assumed that the Windows Auth would work and use the
> access
>> >I had provided to the db on that server.
>> > John Hann
>> > MOM MVP
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> >> When you setup a datasource there is a tab that says credentials. You
> are
>> >> telling RS how to access the data for the report. For instance, I go
>> >> against
>> >> Sybase a lot. I set it to a special username and password that is a
>> >> readonly
>> >> username. Although you can tell it to use the username and password of
>> >> the
>> >> person running the report, I recommend against doing that. If you can
> use
>> >> a
>> >> set username and password then the system will pool connections and it
>> >> can
>> >> reuse a connection rather than create a new one for each user.
>> >>
>> >> Read up on credentials in the help.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> >> Its set as Windows Auth...Any special access I need on the DB at the
>> >> other
>> >> server?
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> >> > My guess is a problem with how you have set up your credentials for
>> >> > the
>> >> > data
>> >> > source.
>> >> >
>> >> > --
>> >> > Bruce Loehle-Conger
>> >> > MVP SQL Server Reporting Services
>> >> >
>> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> >> I have always used Reporting Services to point to a DB on the same
>> >> server
>> >> > as
>> >> >> RS.
>> >> >>
>> >> >> If I set the datasource to another server I get:
>> >> >>
>> >> >> Reporting Services Error
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >> a.. An error has occurred during report processing.
>> >> > (rsProcessingAborted)
>> >> >> Get Online Help
>> >> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> >> (rsErrorOpeningConnection) Get Online Help
>> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
> a
>> >> > trusted
>> >> >> SQL Server connection.
>> >> >>
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >>
>> >> >> How do I set this as a trusted connection?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Man...That worked, but not the supplied credentials in the datasource
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJ%23FJ6ZzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Have you go into the datasource via Report Manager, properties? You should
> see something like this for credentials:
> Connect Using:
> The credentials supplied by the user running the report
> Display the following text to prompt user for a login name and
> password:
> Use as Windows credentials when connecting to the data
> source
> Credentials stored securely in the report server
>
> To make sure everything is working just pick the first one. Then if you
> can
> get to the database using query analyzer then report server can to. As I
> mentioned before, I prefer specifying a single readonly user for the
> credentials.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John Hann [MVP]" <blah@.blah.blah> wrote in message
> news:OMRdpwZzEHA.3844@.TK2MSFTNGP12.phx.gbl...
>> Hmm...Still getting the Not a trusted connection with the local user id
>> to
>> the SQL server...
>>
>> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> news:uyDXwpZzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>> >I see. I just assumed that the Windows Auth would work and use the
> access
>> >I had provided to the db on that server.
>> > John Hann
>> > MOM MVP
>> >
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:Ooe9WRZzEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> >> When you setup a datasource there is a tab that says credentials. You
> are
>> >> telling RS how to access the data for the report. For instance, I go
>> >> against
>> >> Sybase a lot. I set it to a special username and password that is a
>> >> readonly
>> >> username. Although you can tell it to use the username and password of
>> >> the
>> >> person running the report, I recommend against doing that. If you can
> use
>> >> a
>> >> set username and password then the system will pool connections and it
>> >> can
>> >> reuse a connection rather than create a new one for each user.
>> >>
>> >> Read up on credentials in the help.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> news:e49kiAZzEHA.3596@.TK2MSFTNGP12.phx.gbl...
>> >> Its set as Windows Auth...Any special access I need on the DB at the
>> >> other
>> >> server?
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:OziGkwYzEHA.2716@.TK2MSFTNGP14.phx.gbl...
>> >> > My guess is a problem with how you have set up your credentials for
>> >> > the
>> >> > data
>> >> > source.
>> >> >
>> >> > --
>> >> > Bruce Loehle-Conger
>> >> > MVP SQL Server Reporting Services
>> >> >
>> >> > "John Hann [MVP]" <blah@.blah.blah> wrote in message
>> >> > news:%23vaUvnYzEHA.924@.TK2MSFTNGP10.phx.gbl...
>> >> >> I have always used Reporting Services to point to a DB on the same
>> >> server
>> >> > as
>> >> >> RS.
>> >> >>
>> >> >> If I set the datasource to another server I get:
>> >> >>
>> >> >> Reporting Services Error
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >> a.. An error has occurred during report processing.
>> >> > (rsProcessingAborted)
>> >> >> Get Online Help
>> >> >> a.. Cannot create a connection to data source 'dbAudit'.
>> >> >> (rsErrorOpeningConnection) Get Online Help
>> >> >> a.. Login failed for user 'Bha'. Reason: Not associated with
> a
>> >> > trusted
>> >> >> SQL Server connection.
>> >> >>
>> >>
>> ----
> --
>> >> -
>> >> > --
>> >> >>
>> >> >>
>> >> >> How do I set this as a trusted connection?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>
>|||You might want to check out delegation, it lets you propagate a windows
authentication token from one machine to another. I haven't tried it yet but
i suspect you need to enable delegation for the report server to send the
windows authentication to the data source server. This assumes you are using
windows authentication with an active directory domain.
Please post back here if this is of some help, i intend to try it here some
day when we have more time.
"John Hann [MVP]" wrote:
> I have always used Reporting Services to point to a DB on the same server as
> RS.
> If I set the datasource to another server I get:
> Reporting Services Error
> ----
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'dbAudit'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Login failed for user 'Bha'. Reason: Not associated with a trusted
> SQL Server connection.
> ----
>
> How do I set this as a trusted connection?
>
>

Data Source Credentials problem

Hi guys.

I've got an Xml DataSource in my report server, used to talk to a web service to retrieve it's data. Now, since this is an Xml DataSource that talks to a Web service, i shouldn't have to give it credentials to run with, or - since the web service runs on the machine right next to my report server - I could use Windows Integrated Auth.

However ... when I try to use Windows Integrated auth. I get back the error:

"This

data source is configured to use Windows integrated security. Windows integrated

security is either disabled for this report server or your report server is

using Trusted Account mode. "


And when I tried to use no credentials, before I can even see the report, I get the error:

"the user data source credentials that are required to execute this report are

not stored in the report server database." - where I just told it that it doesn't need credentials to run this datasource !!!

For the first error you got, there are two possible causes:

1. If you are running RS in sharepoint integrate mode, make sure the authentication mode you set in sharepoint central admin is Windows Integrated, not Trusted Account mode.

2. If you are running RS in non-sharepoint integrate mode, make sure this system properties is set to be true: "EnableIntegratedSecurity".

For the second error, if you set to be none, RS uses a fixed credential configured in Report Server config file to connect to your data source, you have to add it.

|||For the second issue make sure that unattended execution account is initialized. Open Reporting Services Config Manager and goto Execution Account tab|||No, I'm not running in Sharepoint Integrated mode, but I'll try the second resolution and let you guys know what happened, thanx :)|||

Well, here goes the whole story ...

First of all, we're using the Forms Authentication sample in our installation. Thought I should mention that in view of the error I'm about to write ...

Well, I've used the R. Services Configuration tool to set the execution account to a user in my domain. The config tool accepted it fine, only when I tried to run the report I got back:

|||

Finally, i got it to work !!! I created a new user account fot the execution account, one that had no xml special characters in it's uname/pwd and it worked fine.

Now, you guys should forward this to the implementing team because it's just rediculous ...

If the username / password of the Execution Account has any sort of "XML special characters" in it, the WMIProvider will fail to set the exec. account details. Although those credentials actually get written encrypted in the xml config file, so that doesn't seem really necessary.

Anyway, that WOULD be ok, u n l e s s ... if you try to type in the credentials, replacing those special characters with their &xxx; equivalents, the tool will succeed, and write out those details encrypted in the xml file. BUT !!! It won't replace the &amp; to an & in the encrypted string it writes out, so when you try to run reports, login will fail, and you're left there wondering what went wrong.

data provider cannot be found

Hi there,

I am trying to use the new ListView control in VS2008 in an ASP.Net app. My problem starts already with the datasource. I drag and drop a SqlDataSource and select

Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5)

I can connect to the database and test the query in the wizard (I do not need to use a password??)

This is the webconfig entry

<add name="ConnectionString" connectionString="Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf" providerName="Microsoft.SqlServerCe.Client.3.5"/>

I add the following line in the page load event

AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

However I get the error message that the data provider cannot be found and it might not be installed. I added the reference and also gave the folder above ASP.NET full access rights.

Anyone else encountered this problem. It is quiet strange? Thanks a lot for your help.

Cheers,

Chris

Hi,

From your description, it seems that you are wondering why the database creating wizard doesn't ask for your userid and password, and another question is why does you application can't find the data provider, right?

For you first question, I think what the database you created is in Windows Authentication mode, so when you create a database connection wizard, the wizard connects to your database by your windows account. If you want to change the windows authentication mode, you may open your sqlserver enterprise manager, and change the authentication mode to SQL mixed authentication mode in the security part.

Second, from the provider in your connection string, you are using .NET Framework Data Provider for Microsoft SQL Server Compact 3.5. But the application you created is a ASP.NET application, so are you sure you want to connect a SQLServer compact edition. Generally, you should create a SQLServer database and use "System.Data.SqlClient" as the providername in your connection string.

Thanks.

|||

Hi Michael,

of course you are right. I realised yesterday that I just use the wrong data provider. Stupid me. All works fine now.

Thanks again,

Cheers,

Chris

sql

Data not displayed in Preview Tab, but is displayed in Data Tab

This has happened on more than one occasion. I have defined a stored
procedure type datasource with 12 parameters. When I execute the SP in the
Data tab, the resulting rows are correctly displayed. When I use the same
parameters when previewing the report, no data is displayed. I have found
that empty string parameters need to be <blank> on the data tab, but need to
be '' on the preview tab, so I know about this little gotcha.
Has anyone else had this problem and found a solution. I have check the rdl
file to make sure that the datasource and fields are correct. I have a table
and a pie chart on the report and both are empty when I preview it.
===================== Thanks!
Keith
p.s. Get off the couch and exercise!I have answered my own question.
This might come in handy for a few people. If one of your parameters is a
string and the SP allows an empty string, then make sure to check the
Parameter definition box "Allow Blank Value". otherwise if you try to send
it a blank or '' (empty string), then SQL profiler shows the parameter as
N'''. This solved my problem and I am now getting data.
=====================Thanks!
Keith
p.s. Get off the couch and exercise!
"Keith" wrote:
> This has happened on more than one occasion. I have defined a stored
> procedure type datasource with 12 parameters. When I execute the SP in the
> Data tab, the resulting rows are correctly displayed. When I use the same
> parameters when previewing the report, no data is displayed. I have found
> that empty string parameters need to be <blank> on the data tab, but need to
> be '' on the preview tab, so I know about this little gotcha.
> Has anyone else had this problem and found a solution. I have check the rdl
> file to make sure that the datasource and fields are correct. I have a table
> and a pie chart on the report and both are empty when I preview it.
>
> =====================> Thanks!
> Keith
> p.s. Get off the couch and exercise!