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 error Login Failed

I am using Analysis Services and SQL 2000. On my Analysis Services machine I
define my data source to point to a remote SQL 2000 server using windows
authentication with my domain admin account. I can create the cube and
everything looks great but when I process the cube I get login errors. data
source provider error Login failed for user '\\domain\computer$' 42000.
Surely, this is an easy fix.
Thanks,
Kevin E.
Processing is actually done by the MSSQLServerOLAPService service
(msmdsrv.exe).
It is not based on your interactive credentials (like browsing, etc.)
You need to modify the service to use your domain account.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>I am using Analysis Services and SQL 2000. On my Analysis Services machine
>I define my data source to point to a remote SQL 2000 server using windows
>authentication with my domain admin account. I can create the cube and
>everything looks great but when I process the cube I get login errors.
>data source provider error Login failed for user '\\domain\computer$'
>42000. Surely, this is an easy fix.
> Thanks,
> Kevin E.
>
|||I'm having the same problem now... how do you modify this
MSSQLServerOLAPService service? thanks.
"Dave Wickert [MSFT]" wrote:

> Processing is actually done by the MSSQLServerOLAPService service
> (msmdsrv.exe).
> It is not based on your interactive credentials (like browsing, etc.)
> You need to modify the service to use your domain account.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "KevinE" <eckart_612@.hotmail.com> wrote in message
> news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>
>
|||Go into your services on the computer that is accessing the data source and
go to the properties of MSSQLServerOLAPService. In the login tab, change the
user from local system account to a domain level account.
KevinE.
"G. Kumar" <GKumar@.discussions.microsoft.com> wrote in message
news:607F313E-A43E-494C-B8F7-214C8320C013@.microsoft.com...[vbcol=seagreen]
> I'm having the same problem now... how do you modify this
> MSSQLServerOLAPService service? thanks.
> "Dave Wickert [MSFT]" wrote:

data source error Login Failed

I am using Analysis Services and SQL 2000. On my Analysis Services machine I
define my data source to point to a remote SQL 2000 server using windows
authentication with my domain admin account. I can create the cube and
everything looks great but when I process the cube I get login errors. data
source provider error Login failed for user '\\domain\computer$' 42000.
Surely, this is an easy fix.
Thanks,
Kevin E.Processing is actually done by the MSSQLServerOLAPService service
(msmdsrv.exe).
It is not based on your interactive credentials (like browsing, etc.)
You need to modify the service to use your domain account.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>I am using Analysis Services and SQL 2000. On my Analysis Services machine
>I define my data source to point to a remote SQL 2000 server using windows
>authentication with my domain admin account. I can create the cube and
>everything looks great but when I process the cube I get login errors.
>data source provider error Login failed for user '\\domain\computer$'
>42000. Surely, this is an easy fix.
> Thanks,
> Kevin E.
>|||I'm having the same problem now... how do you modify this
MSSQLServerOLAPService service? thanks.
"Dave Wickert [MSFT]" wrote:

> Processing is actually done by the MSSQLServerOLAPService service
> (msmdsrv.exe).
> It is not based on your interactive credentials (like browsing, etc.)
> You need to modify the service to use your domain account.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "KevinE" <eckart_612@.hotmail.com> wrote in message
> news:i8Sdnbpqcc1h0hbfRVn-sA@.centurytel.net...
>
>|||Go into your services on the computer that is accessing the data source and
go to the properties of MSSQLServerOLAPService. In the login tab, change the
user from local system account to a domain level account.
KevinE.
"G. Kumar" <GKumar@.discussions.microsoft.com> wrote in message
news:607F313E-A43E-494C-B8F7-214C8320C013@.microsoft.com...[vbcol=seagreen]
> I'm having the same problem now... how do you modify this
> MSSQLServerOLAPService service? thanks.
> "Dave Wickert [MSFT]" wrote:
>

Data Retrieval

How does SQL handle data retrieval from a specific record? I want to be able
to capture 100 records from any given point in a table by sending a single
column value as a starting point.
So if I have a table with primary_key with values 1-1000 and I want to get
1-100 I can use TOP 100. If I then want 101 - 200, 201 - 300, etc, how can I
get the set(s)?
Can this be done?
Thank you,
AnthonyUse WHERE clause.
declare @.i int, @.j int
set @.i = 201
set @.j = 300
...
where pk_col between @.i and @.j;
AMB
"Anthony W DiGrigoli" wrote:

> How does SQL handle data retrieval from a specific record? I want to be ab
le
> to capture 100 records from any given point in a table by sending a single
> column value as a starting point.
> So if I have a table with primary_key with values 1-1000 and I want to get
> 1-100 I can use TOP 100. If I then want 101 - 200, 201 - 300, etc, how can
I
> get the set(s)?
> Can this be done?
> Thank you,
> Anthony

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
Phil
Inline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.

> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.

> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.

> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.

> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA

>

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixtur
e
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocat
e
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.

> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.

> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.

> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.

> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA

>

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.
> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.
> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.
> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.
> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA
>