servers and permissions

Please forgive my ignorance - I'm new at this...
I have a user who needs permissions to alter properties on linked servers.
The only way I can see to do this is to grant this user's login the sysadmin
role. I really don't want to do this, as the user should not have that leve
l
of permissions on some of the local databases.
Is there a way to grant the user admin permissions on the linked servers but
not using sysadmin?
Thanks!Hi Cyd,
It depends on what they need to modify and what version of
SQL Server we're talking about.
I'm going to guess it's SQL 2000. I haven't tried this but
have you tried adding the user to the serveradmin server
role and giving permissions to sysservers in the master
database? Some of the actions when changing linked server
properties in Enterprise Manager just execute sp_configure
to allow updates to system tables and then the actions
update the sysservers table.
-Sue
On Mon, 30 Oct 2006 10:44:02 -0800, Cyd
<Cyd@.discussions.microsoft.com> wrote:

>Please forgive my ignorance - I'm new at this...
>I have a user who needs permissions to alter properties on linked servers.
>The only way I can see to do this is to grant this user's login the sysadmi
n
>role. I really don't want to do this, as the user should not have that lev
el
>of permissions on some of the local databases.
>Is there a way to grant the user admin permissions on the linked servers bu
t
>not using sysadmin?
>Thanks!|||I should have specified - it's SQL Server 2005 - my apologies...
"Sue Hoegemeier" wrote:

> Hi Cyd,
> It depends on what they need to modify and what version of
> SQL Server we're talking about.
> I'm going to guess it's SQL 2000. I haven't tried this but
> have you tried adding the user to the serveradmin server
> role and giving permissions to sysservers in the master
> database? Some of the actions when changing linked server
> properties in Enterprise Manager just execute sp_configure
> to allow updates to system tables and then the actions
> update the sysservers table.
> -Sue
> On Mon, 30 Oct 2006 10:44:02 -0800, Cyd
> <Cyd@.discussions.microsoft.com> wrote:
>
>|||You would want to look at ALTER ANY LINKED SERVER
permissions for configuration changes and ALTER ANY LOGIN
for adding or dropping security mappings.
-Sue
On Tue, 31 Oct 2006 12:01:01 -0800, Cyd
<Cyd@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I should have specified - it's SQL Server 2005 - my apologies...
>"Sue Hoegemeier" wrote:
>

servers

I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
was created recently by restoring all the system and user databases from
another server which has now been retired. Now I have both the servers linked
from the other server and they both show up in the Enterprise Manager.
However, I can only access the server running SP4 from the server running
SP3, not vice versa. It gives an error:
Server: Msg 17, Level 16, State 1, Line 2
SQL Server does not exist or access denied.
Any insight will be greatly appreciated. Thanks.Are you able to ping old server from the new one?
Manu
"sharman" wrote:
> I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
> was created recently by restoring all the system and user databases from
> another server which has now been retired. Now I have both the servers linked
> from the other server and they both show up in the Enterprise Manager.
> However, I can only access the server running SP4 from the server running
> SP3, not vice versa. It gives an error:
> Server: Msg 17, Level 16, State 1, Line 2
> SQL Server does not exist or access denied.
> Any insight will be greatly appreciated. Thanks.

servers

Dear All,
I am trying to link 2 servers together and get the following message:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
If I query MyServer and OtherServer independantly I can see the expected
results but when I run the following query I get:
select
Ref
from
MyServer.MyDB.dbo.MyTable inner join
OtherServer.OtherDB.dbo.OtherTable T on T.Ref = Ref
I have tried sp_addlinkedserver 'OtherServer'. This generated no error. Can
someone suggest what I am doing incorrectly.
Thanks again...
Alastair MacFarlaneAlastair MacFarlane,
You will have to elaborate a little bit more about your configuration.
- OS version in each server?
- SQL Server version in each server?
- What kind of authentication is being used in each sql server?
AMB
"Alastair MacFarlane" wrote:

> Dear All,
> I am trying to link 2 servers together and get the following message:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> If I query MyServer and OtherServer independantly I can see the expected
> results but when I run the following query I get:
> select
> Ref
> from
> MyServer.MyDB.dbo.MyTable inner join
> OtherServer.OtherDB.dbo.OtherTable T on T.Ref = Ref
> I have tried sp_addlinkedserver 'OtherServer'. This generated no error. Ca
n
> someone suggest what I am doing incorrectly.
> Thanks again...
> Alastair MacFarlane

servers

I have created a link between server A and server B
(sever A contains the link info)
I set the default database of the user on server B to be the database I want
to link to on server B.
Inside Enterprise Manager I can view the tables on server B via the link
icon on server A.
When I start Visual Studio and go to the server explorer to design a query
using the GUI interface, when I try to code:
SEVER_NAME.DATABASE.DBO.TABLE or
SERVER_NAME.DBO.TABLE
(Thinking that if the user on the linked servers default database is set,
then no need to specified in the query?)
inside the query it aliases the table name but not columns display in the
design view, just the *. Trying to run the query said that the Name can only
be three parts or invalid object name SERVER_NAME.dbo.TABLE
Like I said I can view the tables via the linking server though EM. My
developers need to be able to use the VS gui to design their queries. I ran
into situations in the past were if I typed the query straight (not using
gui), and try to save it, I would get a message stated that the MS DTC could
not be started, when I know for a fact both server A & B have the DTC runnin
g.
Can anyone provide assistance?SQL Server Service Manager (SQL 2000), and SQL Server Configuration Manager
(SQL 2005).
ML
http://milambda.blogspot.com/|||SQL Server
DTC
and the SQL Ageent are all running.
Where else do I need to look or configure
--
JP
.NET Software Develper
"ML" wrote:

> SQL Server Service Manager (SQL 2000), and SQL Server Configuration Manage
r
> (SQL 2005).
>
> ML
> --
> http://milambda.blogspot.com/

server: Error 17: SQL Server does not exist or access denied.

I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password.

However when I click to the Tables icon of linked server in Enterprise manager, there is an error message:

Error 17 SQL Server does not exist or access denied.

And It does not show any table.

I register remote sql server in Enterprise manager fine.

Could any one help me ?

Thanks.

Hi,

What remote login did u used for linked servers.

The remote login should exists on both the server.

Regards

Mohd Sufian

|||

S1 - local server
S2 - remote server

1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.

2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.

|||

both S1 and S2 have the same user id and password.

I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17

Do you have any more suggesstion ?

|||

Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?

Thanks
Laurentiu

|||

Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp

Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp

At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:

select * from QLNSParent.qlns.dbo.hoso

It alway returns the error message:

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.

Do you have any suggestion ?

Thanks.

|||

Can you post the output of the following commands on the 237 server?

exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'

Thanks
Laurentiu

|||

the result of the first command:

name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0

and the result of the second command:

Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa

I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.

can you help me ?

|||

Dear ,

Do u have a named instance on sqlserver2005.

from

sufian

|||

Both server run windows server 2003 enterprise edition.

In each server, we have a named instance of Sql server 2000.

|||

Dear,

Connect by give the servername with instance.

ex: svt_test(server name\sql_2000(instance name)

from

sufian

|||

I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?

Thanks
Laurentiu

|||

sp_addlinkedserver @.server = N'QLNSParent',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'10.0.10.111',
@.catalog = N'qlns',
@.provstr = N''
GO
sp_addlinkedsrvlogin @.rmtsrvname = 'QLNSParent',
@.useself = false,
@.locallogin = 'sa',
@.rmtuser = 'sa',
@.rmtpassword = 'vbsp'

GO

do you have any more suggesstion ?

|||

Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?

If the name is ok, have you tried simply specifying:

sp_addlinkedserver N'QLNSParent', N'SQL Server'

Thanks
Laurentiu

server: Error 17: SQL Server does not exist or access denied.

I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password. However when I click to the Tables icon of linked server in Enterprise manager, there is an error message: Error 17 SQL Server does not exist or access denied. And It does not show any table. I register remote sql server in Enterprise manager fine. Could any one help me ? Thanks.

Hi,

What remote login did u used for linked servers.

The remote login should exists on both the server.

Regards

Mohd Sufian

|||

S1 - local server
S2 - remote server

1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.

2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.

|||

both S1 and S2 have the same user id and password.

I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17

Do you have any more suggesstion ?

|||

Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?

Thanks
Laurentiu

|||

Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp

Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp

At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:

select * from QLNSParent.qlns.dbo.hoso

It alway returns the error message:

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.

Do you have any suggestion ?

Thanks.

|||

Can you post the output of the following commands on the 237 server?

exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'

Thanks
Laurentiu

|||

the result of the first command:

name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0

and the result of the second command:

Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa

I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.

can you help me ?

|||

Dear ,

Do u have a named instance on sqlserver2005.

from

sufian

|||

Both server run windows server 2003 enterprise edition.

In each server, we have a named instance of Sql server 2000.

|||

Dear,

Connect by give the servername with instance.

ex: svt_test(server name\sql_2000(instance name)

from

sufian

|||

I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?

Thanks
Laurentiu

|||

sp_addlinkedserver @.server = N'QLNSParent',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'10.0.10.111',
@.catalog = N'qlns',
@.provstr = N''
GO
sp_addlinkedsrvlogin @.rmtsrvname = 'QLNSParent',
@.useself = false,
@.locallogin = 'sa',
@.rmtuser = 'sa',
@.rmtpassword = 'vbsp'

GO

do you have any more suggesstion ?

|||

Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?

If the name is ok, have you tried simply specifying:

sp_addlinkedserver N'QLNSParent', N'SQL Server'

Thanks
Laurentiu

|||Hi, vhamu

I have the same problem from linking servers, did you find solution of this problem ?

Thanks

server VFPOLEDB gives Access Denied error

When I login to the windows server as administrator, the query runs. When I
login as anyone else (incl a user with as many admin rights as I can give
him), I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "A" reported an error.
Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
provider "VFPOLEDB" for linked server "A".
Any help would be appreciated."Ashley Brewerton" <AshleyBrewerton@.discussions.microsoft.com> wrote in
message news:800A97D6-2A65-4A2C-A746-56A4D4931C2A@.microsoft.com...
Hi Ashley,
Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
Here's what works for me to set up the Linked Server in SQL 2005 using the
Linked Server dialog:
Providers list: VFPOLEDB shows as an entry
Linked Servers > New Linked Server ...
Linked Server: MyLinkedServer
Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro
Product name: Visual FoxPro (I think this is optional)
Data source: "C:\Program Files\Microsoft Visual
FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
spaces in the string.)
Provider string: VFPOLEDB.1
Location: (blank)
Catalog: (blank)
Security page and Server Options page take defaults.
> When I login to the windows server as administrator, the query runs. When
> I
> login as anyone else (incl a user with as many admin rights as I can give
> him), I get the following error:
What permissions does the SQL Server local system account have?
I'm ok on my laptop with the SQL Server started with "local
system account" and my Windows login being MachineName\Cindy. When I tried
against my SQL Express instance which was started using the "NT
AUTHORITY\NetworkService" I had the same errors you report.
> Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> provider "VFPOLEDB" for linked server "A".
How are you executing your query? With the four-part naming or using
OpenQuery()?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com|||Hi Cindy
Yes, I saw your previous post and have already tried the setup you
indicated. I run the query using the standard select syntax::
select * from AURORA...CMaster
"Cindy Winegarden" wrote:
> "Ashley Brewerton" <AshleyBrewerton@.discussions.microsoft.com> wrote in
> message news:800A97D6-2A65-4A2C-A746-56A4D4931C2A@.microsoft.com...
> Hi Ashley,
> Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
> downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
> Here's what works for me to set up the Linked Server in SQL 2005 using the
> Linked Server dialog:
> Providers list: VFPOLEDB shows as an entry
> Linked Servers > New Linked Server ...
> Linked Server: MyLinkedServer
> Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro
> Product name: Visual FoxPro (I think this is optional)
> Data source: "C:\Program Files\Microsoft Visual
> FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
> spaces in the string.)
> Provider string: VFPOLEDB.1
> Location: (blank)
> Catalog: (blank)
> Security page and Server Options page take defaults.
> > When I login to the windows server as administrator, the query runs. When
> > I
> > login as anyone else (incl a user with as many admin rights as I can give
> > him), I get the following error:
> What permissions does the SQL Server local system account have?
> I'm ok on my laptop with the SQL Server started with "local
> system account" and my Windows login being MachineName\Cindy. When I tried
> against my SQL Express instance which was started using the "NT
> AUTHORITY\NetworkService" I had the same errors you report.
> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> > provider "VFPOLEDB" for linked server "A".
> How are you executing your query? With the four-part naming or using
> OpenQuery()?
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
>