how to show data from multiple tables in one report

Hi,
How to show data from multiple tables which are linked in one report. I will
elaborate the problem little bit, please bear with me -
I have a db which contains three tables - ServerInfo, ServiceInfo, TaskInfo
The relation among them is
One server can have multiple services
One server can have multiple task
There is no relation betwen task and services. There could be 'n' services
and 'm' tasks running on the same server.
Moreover, there could be n servers in the system.
I want to create a all server health reports which displays status of the
services and tasks per server. Each server info comes on a separate page.
I tried to do this using data region. But the problem is data region are
bound to one dataset, which doesnt work in my case.
Please help me out in creating this reportRead up on subreports. What you want is perfect to solve this.
A subreport is just a regular report with a parameter that you drag and drop
onto the main report. Then do a right mouse click on the subreport and map
the parameter to the appropriate field. In your case you will have two
subreport. First develop and test the report individually.
When you deploy the reports you can go to report manager, properties for the
subreport and hide them in list view so that your users don't see
unnecessary reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> Hi,
> How to show data from multiple tables which are linked in one report. I
> will
> elaborate the problem little bit, please bear with me -
> I have a db which contains three tables - ServerInfo, ServiceInfo,
> TaskInfo
> The relation among them is
> One server can have multiple services
> One server can have multiple task
> There is no relation betwen task and services. There could be 'n' services
> and 'm' tasks running on the same server.
> Moreover, there could be n servers in the system.
> I want to create a all server health reports which displays status of the
> services and tasks per server. Each server info comes on a separate page.
> I tried to do this using data region. But the problem is data region are
> bound to one dataset, which doesnt work in my case.
> Please help me out in creating this report|||Hi Bruce:
I have same problem with this case!
I try to use subreport to show (for this example, Server Info (1), Service
Info (n) and Tasks (m). It looks very good so far. However when I try to
export it to Excel. All subreport contents cannot be shown. (note that,
preview and export to pdf format is normal)!
please help!
Tony
"Bruce L-C [MVP]" wrote:
> Read up on subreports. What you want is perfect to solve this.
> A subreport is just a regular report with a parameter that you drag and drop
> onto the main report. Then do a right mouse click on the subreport and map
> the parameter to the appropriate field. In your case you will have two
> subreport. First develop and test the report individually.
> When you deploy the reports you can go to report manager, properties for the
> subreport and hide them in list view so that your users don't see
> unnecessary reports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> > Hi,
> >
> > How to show data from multiple tables which are linked in one report. I
> > will
> > elaborate the problem little bit, please bear with me -
> >
> > I have a db which contains three tables - ServerInfo, ServiceInfo,
> > TaskInfo
> > The relation among them is
> > One server can have multiple services
> > One server can have multiple task
> > There is no relation betwen task and services. There could be 'n' services
> > and 'm' tasks running on the same server.
> > Moreover, there could be n servers in the system.
> >
> > I want to create a all server health reports which displays status of the
> > services and tasks per server. Each server info comes on a separate page.
> >
> > I tried to do this using data region. But the problem is data region are
> > bound to one dataset, which doesnt work in my case.
> >
> > Please help me out in creating this report
>
>|||Thanks Bruce !!!
I want to show information about all servers in one report. So this report
does not take any parameter. Moreover I want to group related servers info
per page.
Can I do this using subreports?
What I understood is I will create one master report which will have 'n'
subreports in it. Each subreport will show information about one server.
If above understanding is correct. I have one more question
How the subreport will know the server name for which information is to be
retrieved.
regards,
Sachin.
"Bruce L-C [MVP]" wrote:
> Read up on subreports. What you want is perfect to solve this.
> A subreport is just a regular report with a parameter that you drag and drop
> onto the main report. Then do a right mouse click on the subreport and map
> the parameter to the appropriate field. In your case you will have two
> subreport. First develop and test the report individually.
> When you deploy the reports you can go to report manager, properties for the
> subreport and hide them in list view so that your users don't see
> unnecessary reports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> > Hi,
> >
> > How to show data from multiple tables which are linked in one report. I
> > will
> > elaborate the problem little bit, please bear with me -
> >
> > I have a db which contains three tables - ServerInfo, ServiceInfo,
> > TaskInfo
> > The relation among them is
> > One server can have multiple services
> > One server can have multiple task
> > There is no relation betwen task and services. There could be 'n' services
> > and 'm' tasks running on the same server.
> > Moreover, there could be n servers in the system.
> >
> > I want to create a all server health reports which displays status of the
> > services and tasks per server. Each server info comes on a separate page.
> >
> > I tried to do this using data region. But the problem is data region are
> > bound to one dataset, which doesnt work in my case.
> >
> > Please help me out in creating this report
>
>|||Hi Sachin Laddha,
Subreport is work but I get the following problem:
1, When export to Excel, get error for the subreport
2, When export to pdf, I get the layout problem. If you interest in this
case, pls read the question I ask "Bruce". The question is "Question want to
ask Bruce L-C for report layout!".
Tony
"Sachin Laddha" wrote:
> Thanks Bruce !!!
> I want to show information about all servers in one report. So this report
> does not take any parameter. Moreover I want to group related servers info
> per page.
> Can I do this using subreports?
> What I understood is I will create one master report which will have 'n'
> subreports in it. Each subreport will show information about one server.
> If above understanding is correct. I have one more question
> How the subreport will know the server name for which information is to be
> retrieved.
> regards,
> Sachin.
> "Bruce L-C [MVP]" wrote:
> > Read up on subreports. What you want is perfect to solve this.
> >
> > A subreport is just a regular report with a parameter that you drag and drop
> > onto the main report. Then do a right mouse click on the subreport and map
> > the parameter to the appropriate field. In your case you will have two
> > subreport. First develop and test the report individually.
> >
> > When you deploy the reports you can go to report manager, properties for the
> > subreport and hide them in list view so that your users don't see
> > unnecessary reports.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> > news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> > > Hi,
> > >
> > > How to show data from multiple tables which are linked in one report. I
> > > will
> > > elaborate the problem little bit, please bear with me -
> > >
> > > I have a db which contains three tables - ServerInfo, ServiceInfo,
> > > TaskInfo
> > > The relation among them is
> > > One server can have multiple services
> > > One server can have multiple task
> > > There is no relation betwen task and services. There could be 'n' services
> > > and 'm' tasks running on the same server.
> > > Moreover, there could be n servers in the system.
> > >
> > > I want to create a all server health reports which displays status of the
> > > services and tasks per server. Each server info comes on a separate page.
> > >
> > > I tried to do this using data region. But the problem is data region are
> > > bound to one dataset, which doesnt work in my case.
> > >
> > > Please help me out in creating this report
> >
> >
> >

How to show a linked report in a report container?

I have a report container (reportviewer.dll) in a .net web page to display
the reports using the reporting services. Some of the reportds are linked
report and I am not sure how to display it in the report container.
Currently it opens on a new page. Thanks.I have the same problem...
Any luck with that?
--
http://dotnet.org.za/stanley
"Paul" wrote:
> I have a report container (reportviewer.dll) in a .net web page to display
> the reports using the reporting services. Some of the reportds are linked
> report and I am not sure how to display it in the report container.
> Currently it opens on a new page. Thanks.
>
>

How to Setup Linked Server within Same SQL Server

Been wandering around the searches and cannot find a specific answer so hopefully someone can help me. Basic situation is I'm setting up an automatic transfering of data to a historical database that resides on the same SQL Server.

exec sp_dropserver 'linked1', 'droplogins'
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname 'linked1', <Databasename>
exec sp_addlinkedsrvlogin 'linked1', 'false', null, <user>, <password>

SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'select * from dbo.table')

I run this and it says it cannot find the instance...You cannot specify local server as a linked server.|||If you check the documentation for sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp), it looks like example 2 is almost exactly what you need. Just provide the machine name instead of machine\instance and you'll be in business!

-PatP|||Originally posted by rdjabarov
You cannot specify local server as a linked server. That's strange, it let me do it on my test machine.

-PatP|||What's the SP level on your test? I even decided to try it on one of my Prod servers (SP3a) and it failed just like on my Personal Edition (SP3a).|||My test machine runs at sp2, because many of the third-party applications that I support won't run properly under sp3a. That may well be the difference.

My home machines run at sp3a. I'll have to test it there when I get the chance.

-PatP|||Thank you for all of the replies. I got everything worked out without having to use the linkserver but the help will definitely help me at a later time. Thanks again

Originally posted by Pat Phelan
My test machine runs at sp2, because many of the third-party applications that I support won't run properly under sp3a. That may well be the difference.

My home machines run at sp3a. I'll have to test it there when I get the chance.

-PatP|||I was actually about to suggest, that if it's the same server then you don't need OPENQUERY, or a linked server for that matter. Just a cross-database query would do.

How to Setup Linked Server

Hello:
Would you mind to tell me how can I setup the Link
between 2 SQL server that both server have located in
their local domain?
Thanks a lot!Probably easiest to use Enterprise Manager for this purpose. Expand your
server, expand "Security", click on "Linked Servers", and click the new
icon. You can set up the link from that menu fairly easily.
You can also set up linked servers using the sp_addlinkedserver and
sp_addlinkedsrvlogin stored procedures, which you can find syntax for in
BOL.
"Alan Tang" <alantang@.netband.com.hk> wrote in message
news:3b4501c488b1$f0f19690$a301280a@.phx.gbl...
> Hello:
> Would you mind to tell me how can I setup the Link
> between 2 SQL server that both server have located in
> their local domain?
> Thanks a lot!|||Hi,
Please see the below article.
http://www.microsoft.com/India/msdn/articles/166.aspx
Thanks
Hari
MCDBA
"Alan Tang" <alantang@.netband.com.hk> wrote in message
news:3b4501c488b1$f0f19690$a301280a@.phx.gbl...
> Hello:
> Would you mind to tell me how can I setup the Link
> between 2 SQL server that both server have located in
> their local domain?
> Thanks a lot!

How to setup Link Server properly

Hi,

I want to access another database seating on another server so I configured the Linked server with SQL Server as the server type. I set the security as NT_AUTHORITY\SYSTEM and checked the Impersonate. In Server Options, I set the RPC Out to true then I clicked ok.

I got this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB Provider "SQLNCLI" for linked server "server1" reported an error. Authentication failed.

Cannot initialize the datasource object of OLE DB Provider "SQLNCLI" for linked server "server1".

OLE DB Provider "SQLNCLI" for linked server "server1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)

Note that my NT login is added as "Administrators" on the Server i want to connect to.

Have I missed anything?

cherriesh

I tend to use sp_addlinkedserver and sp_addlinkedsrvlogin for this.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

http://msdn2.microsoft.com/en-us/library/ms189811.aspx

The trick is to specify 'true' for @.useself for sp_addlinkedsrvlogin.

e.g.

Code Snippet

exec sp_addlinkedsrvlogin 'remotesrv', 'true'

how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and[vbcol=seagreen]
> and
> query
> ServerA,
> allow
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be[vbcol=seagreen]
>

how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank you
Steve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>
|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================
ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'[vbcol=seagreen]
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
and
> and
> query
> ServerA,
> allow
>
|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject[vbcol=seagreen]
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================
> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
login[vbcol=seagreen]
> as
> al)
> then
> 'sa'
> and
Chris,[vbcol=seagreen]
be
>

how to set up a secured linked server?

background sql2k on nt5.
sorry to post this again. didn't get a good response for my last post and
really need some advice on this.
I have read that subject in SQL BOL.
and it doesn't make much sense to me.
If I were to have a NT group called sqladmins, and i assign Bob, Chris, and
Doug to that group.
I have 2 servers, ServerA and Server B. I want sqladmis to be able to query
ServerB (any database, such as sysobjects in master database) from ServerA,
but not for other users not belong to that group.
What should I do?
the bottom line is, I'd like to set up linked server securely and only allow
certain users in certain NT group to use it. (since sa password will be
supplied when set up a linked server, i don't want any user to use an
established linked server as they were sa to other servers). is that
possible?
Pls advise! Thank youSteve,
Using sp_addlinkedserverlogin you could have four logins.
1-3 = Bob, Chris, and Doug as such:
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
'OtherServerAdmin, 'OtherServerAdminPassword'
EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
'OtherServerAdmin, 'OtherServerAdminPassword'
4 = for everybody else
EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login as
themselves.
Or, you could only use line 4 and then make the sysadmins group (Bob, et al)
also sysadmins on the other server. If you are letting them in as 'sa' then
they are being sysadmins. (Insert Here: Standard advice to not use the 'sa'
account.)
Russell Fields
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> background sql2k on nt5.
> sorry to post this again. didn't get a good response for my last post and
> really need some advice on this.
> I have read that subject in SQL BOL.
> and it doesn't make much sense to me.
> If I were to have a NT group called sqladmins, and i assign Bob, Chris,
and
> Doug to that group.
> I have 2 servers, ServerA and Server B. I want sqladmis to be able to
query
> ServerB (any database, such as sysobjects in master database) from
ServerA,
> but not for other users not belong to that group.
> What should I do?
> the bottom line is, I'd like to set up linked server securely and only
allow
> certain users in certain NT group to use it. (since sa password will be
> supplied when set up a linked server, i don't want any user to use an
> established linked server as they were sa to other servers). is that
> possible?
> Pls advise! Thank you
>|||thanks for the reply.
what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
can I have more detailed scripts for demonstrating the usage of this subject
based on the following info?
ServerA Name: sql2kt
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
===================ServerB Name: sql2k
has following databases:
finance
HR
and following NT group/account
Domain\SQLAdmins (password is 'pw')
Domain\JohnDoe
My questions:
1. how to establish linked servers (from sql2kt to sql2k, to able to read
data either in finance or HR)?
2. how to allow this connection only used by Domain\SQLAdmins, but not by
Domain\JohnDoe?
Thank you.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> Steve,
> Using sp_addlinkedserverlogin you could have four logins.
> 1-3 = Bob, Chris, and Doug as such:
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> 'OtherServerAdmin, 'OtherServerAdminPassword'
> 4 = for everybody else
> EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to login
as
> themselves.
> Or, you could only use line 4 and then make the sysadmins group (Bob, et
al)
> also sysadmins on the other server. If you are letting them in as 'sa'
then
> they are being sysadmins. (Insert Here: Standard advice to not use the
'sa'
> account.)
> Russell Fields
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > background sql2k on nt5.
> >
> > sorry to post this again. didn't get a good response for my last post
and
> > really need some advice on this.
> >
> > I have read that subject in SQL BOL.
> > and it doesn't make much sense to me.
> >
> > If I were to have a NT group called sqladmins, and i assign Bob, Chris,
> and
> > Doug to that group.
> > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> query
> > ServerB (any database, such as sysobjects in master database) from
> ServerA,
> > but not for other users not belong to that group.
> > What should I do?
> >
> > the bottom line is, I'd like to set up linked server securely and only
> allow
> > certain users in certain NT group to use it. (since sa password will be
> > supplied when set up a linked server, i don't want any user to use an
> > established linked server as they were sa to other servers). is that
> > possible?
> >
> > Pls advise! Thank you
> >
> >
>|||Steve,
Sorry about the lack of scripts, but..
sp_addlinkedserver - defines the link to another server
sp_addlinkedsrvlogin - defines a login (or logins) that will be used in the
link to the other server
The logins that pass through the link need to be authorized on the link
server as well. So, in the case of Domain\Bob you would need to GRANT him
rights to the finance and HR databases (or to the needed views, etc. in
those databases.)
Russell
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OkKa2K4PEHA.3216@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply.
> what's the difference of sp_addlinkedsrvlogin and sp_addlinkedserver?
> can I have more detailed scripts for demonstrating the usage of this
subject
> based on the following info?
> ServerA Name: sql2kt
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> ===================> ServerB Name: sql2k
> has following databases:
> finance
> HR
> and following NT group/account
> Domain\SQLAdmins (password is 'pw')
> Domain\JohnDoe
> My questions:
> 1. how to establish linked servers (from sql2kt to sql2k, to able to read
> data either in finance or HR)?
> 2. how to allow this connection only used by Domain\SQLAdmins, but not by
> Domain\JohnDoe?
> Thank you.
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:%23vhenJ2PEHA.2348@.TK2MSFTNGP10.phx.gbl...
> > Steve,
> >
> > Using sp_addlinkedserverlogin you could have four logins.
> >
> > 1-3 = Bob, Chris, and Doug as such:
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Bob',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Chris',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'false', 'Domain\Doug',
> > 'OtherServerAdmin, 'OtherServerAdminPassword'
> >
> > 4 = for everybody else
> > EXEC sp_addlinkedsrvlogin 'OtherServer', 'true' -- They will try to
login
> as
> > themselves.
> >
> > Or, you could only use line 4 and then make the sysadmins group (Bob, et
> al)
> > also sysadmins on the other server. If you are letting them in as 'sa'
> then
> > they are being sysadmins. (Insert Here: Standard advice to not use the
> 'sa'
> > account.)
> >
> > Russell Fields
> >
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Ohy5wg0PEHA.556@.tk2msftngp13.phx.gbl...
> > > background sql2k on nt5.
> > >
> > > sorry to post this again. didn't get a good response for my last post
> and
> > > really need some advice on this.
> > >
> > > I have read that subject in SQL BOL.
> > > and it doesn't make much sense to me.
> > >
> > > If I were to have a NT group called sqladmins, and i assign Bob,
Chris,
> > and
> > > Doug to that group.
> > > I have 2 servers, ServerA and Server B. I want sqladmis to be able to
> > query
> > > ServerB (any database, such as sysobjects in master database) from
> > ServerA,
> > > but not for other users not belong to that group.
> > > What should I do?
> > >
> > > the bottom line is, I'd like to set up linked server securely and only
> > allow
> > > certain users in certain NT group to use it. (since sa password will
be
> > > supplied when set up a linked server, i don't want any user to use an
> > > established linked server as they were sa to other servers). is that
> > > possible?
> > >
> > > Pls advise! Thank you
> > >
> > >
> >
> >
>

How to set rights in E.Manager Since it is a open book..

Hi,

For working with linked servers , Mr. Hills had replied like , one can
not see the login rights etc if the particular user is not in the
admin role in the Enter prise manager.

Since the E.Manager is a open book so that any one can access any
database , Do you explain me When the sql server E.manager ks the
user's login and password .

With thanks

RAGHUWhen you register a server in EM you specify an authentication mode.

If you choose Windows Authentication then the login information is taken
from your Windows domain login - you will have access only to the databases
to which your login has been granted permissions.

If you choose SQL Server Authentication then you can opt either to save the
login name and password in the registry or to prompt for a login name each
time you try to connect to the server in EM. Your level of access is
determined by the SQL Server login name supplied.

For maximum security use Windows Authentication or set the option to prompt
for a login name each time you connect. If you choose Windows Authentication
then also password protect your screen saver so that your PC is secure when
you are away.

Does that answer your question?

--
David Portas
----
Please reply only to the newsgroup
--