Link Server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks,
JamieHi
If you don't call sp_addlinkedsvrlogin, then you will have a default match
between logins from ServerA to ServerB created by sp_addlinkedserver. Have
you checked that you have access with the current login to serverA on serverB?
You may want to try calling
EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
John
"Jamie" wrote:
> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select *
> from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop the
> linked server and readd the linked server again. I got the same result.
> Who can help a bit?
> Thanks,
> Jamie
>
>|||Thank you John! I just found out the real problem. We have IP Address
problem of SQLB. After it is fixed, I can ran query across servers.
Jamie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2BD42FE7-07D4-4C46-9D93-2E01CBC87E8A@.microsoft.com...
> Hi
> If you don't call sp_addlinkedsvrlogin, then you will have a default match
> between logins from ServerA to ServerB created by sp_addlinkedserver. Have
> you checked that you have access with the current login to serverA on
> serverB?
> You may want to try calling
> EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
> John
> "Jamie" wrote:
>> Hi, there,
>> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
>> just a bald SQL Server.
>> I want to set up linked server from SQLA , so I can run query like
>> "select *
>> from SQLB.dbname.dbo.tablename". The command I use is
>> sp_addlinkedserver @.server = 'SQLB'
>> After I ran this code, I span the "Linked servers" node of Enterprise
>> Manager, I saw an image of a linked server named "SQLB". Then I run the
>> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
>> SQLA, I got the following errors
>> Server: Msg 17, Level 16, State 1, Line 1
>> SQL Server does not exist or access denied.
>> I'm the system administrator of the two servers, and the account I ran
>> the
>> query is sys admin account. I don't know why the command failed. I drop
>> the
>> linked server and readd the linked server again. I got the same result.
>> Who can help a bit?
>> Thanks,
>> Jamie
>>
>>

Link Server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks,
Jamie
Hi
If you don't call sp_addlinkedsvrlogin, then you will have a default match
between logins from ServerA to ServerB created by sp_addlinkedserver. Have
you checked that you have access with the current login to serverA on serverB?
You may want to try calling
EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
John
"Jamie" wrote:

> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select *
> from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop the
> linked server and readd the linked server again. I got the same result.
> Who can help a bit?
> Thanks,
> Jamie
>
>
|||Thank you John! I just found out the real problem. We have IP Address
problem of SQLB. After it is fixed, I can ran query across servers.
Jamie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2BD42FE7-07D4-4C46-9D93-2E01CBC87E8A@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't call sp_addlinkedsvrlogin, then you will have a default match
> between logins from ServerA to ServerB created by sp_addlinkedserver. Have
> you checked that you have access with the current login to serverA on
> serverB?
> You may want to try calling
> EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
> John
> "Jamie" wrote:

Link Server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks,
JamieHi
If you don't call sp_addlinkedsvrlogin, then you will have a default match
between logins from ServerA to ServerB created by sp_addlinkedserver. Have
you checked that you have access with the current login to serverA on server
B?
You may want to try calling
EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
John
"Jamie" wrote:

> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select
*
> from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop th
e
> linked server and readd the linked server again. I got the same result.
> Who can help a bit?
> Thanks,
> Jamie
>
>|||Thank you John! I just found out the real problem. We have IP Address
problem of SQLB. After it is fixed, I can ran query across servers.
Jamie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2BD42FE7-07D4-4C46-9D93-2E01CBC87E8A@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't call sp_addlinkedsvrlogin, then you will have a default match
> between logins from ServerA to ServerB created by sp_addlinkedserver. Have
> you checked that you have access with the current login to serverA on
> serverB?
> You may want to try calling
> EXEC sp_addlinkedsrvlogin 'SQLB', 'true'
> John
> "Jamie" wrote:
>

Limited Remote Login db access

I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>\sqlexpress\userid I can only see the system db's...I am trying to view adventure works, which I can see on the desktop fine using the same login...any ideas?

Thanks,

Rob

Do you have a domain controller and home network?

If not, then you must create use SQL logins -you cannot use Windows authentication without an Active Directory (domain controller).

|||

I don't even know what those things mean.

How would I go about doing this with SQL logins?

Rob

|||

I suggest you follow the steps under "To configure AdventureWorks to work with Web server controls" on the following page from the Books Online.

http://msdn2.microsoft.com/en-us/library/ms310325(vs.80).aspx

Hopefully this helps,

John

|||

So I read over the link provided...

I have completed the first part and added it to the server. I can access the database on my desktop machine, when I login from the laptop however (using the same windows login) I can't access to database...do I need to also add something to make it accessible remotely? I don't really understand what "configure AdventureWorks to work with Web server controls
" means..

Rob

|||

These resources might help.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

|||

I have done this...as I stated before I can connect fine its just that I can't see anything other than system db's...I imagine I'm missing something stupid, like perhaps I didn't configure the login correctly?

Rob

|||

The login needs to be provided permission to access the databases.

Add the login to the appropriate database role.

|||

Ok first...I am running XP home on both the desktop and laptop...and the logins are windows auth.

if I want full access from the remote connection, where do I change the role of the login? is it in the login menu or the databases menu?

Rob

|||

Look back at the first response given to your problem.

Windows authentication will not work in your situation -you do not have a domain controller.

You will need to establish SQL Logins in each server, and assign permissions accordingly.

Look in Books Online about creating SQL Logins.

Limited Remote Login access

Hello all,

I'm very new to this so excuse my naiveity...I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>\sqlexpress\userid I can only see the systemdb's...I am trying to view adventure works, just to see how this remote connection stuff works...any ideas?

Thanks,

Rob

See my response to your post in the 'Data Access' forum.

Limitations sql server 2000 Personal edition

SQL Server Personal edition
Enterprise Manager
on Win 2000
I have the above installed on my own computer
I have connected to a remote database using it's IP adress
I can see all the tables - eg NorthWind
With the appropriate login (Server Authentification) should I be able to
create a new database, Create new tables, see permissions and alter them ?
[I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
situation can see but I cannaot do anything else!]
BUT have been told my login will allow this!!!!!
[I have also tried connecting through Query Analyser - no creates allowed]
Jim Bunton
Ok - sorted thsi no replies required thanks
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:XK37f.173379$RW.126302@.fe2.news.blueyonder.co .uk...
> SQL Server Personal edition
> Enterprise Manager
> on Win 2000
> I have the above installed on my own computer
> I have connected to a remote database using it's IP adress
> I can see all the tables - eg NorthWind
> With the appropriate login (Server Authentification) should I be able to
> create a new database, Create new tables, see permissions and alter them
?
> [I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
> situation can see but I cannaot do anything else!]
> BUT have been told my login will allow this!!!!!
> [I have also tried connecting through Query Analyser - no creates allowed]
> --
>
> Jim Bunton
>

Limitations sql server 2000 Personal edition

SQL Server Personal edition
Enterprise Manager
on Win 2000
I have the above installed on my own computer
I have connected to a remote database using it's IP adress
I can see all the tables - eg NorthWind
With the appropriate login (Server Authentification) should I be able to
create a new database, Create new tables, see permissions and alter them ?
[I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
situation can see but I cannaot do anything else!]
BUT have been told my login will allow this!!!!!
[I have also tried connecting through Query Analyser - no creates allowe
d]
--
Jim BuntonOk - sorted thsi no replies required thanks
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:XK37f.173379$RW.126302@.fe2.news.blueyonder.co.uk...
> SQL Server Personal edition
> Enterprise Manager
> on Win 2000
> I have the above installed on my own computer
> I have connected to a remote database using it's IP adress
> I can see all the tables - eg NorthWind
> With the appropriate login (Server Authentification) should I be able to
> create a new database, Create new tables, see permissions and alter them
?
> [I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
> situation can see but I cannaot do anything else!]
> BUT have been told my login will allow this!!!!!
> [I have also tried connecting through Query Analyser - no creates allo
wed]
> --
>
> Jim Bunton
>

Limitations Server 2000 Personal Edition

SQL Server Personal edition
Enterprise Manager
on Win 2000
I have the above installed on my own computer
I have connected to a remote database using it's IP adress
I can see all the tables - eg NorthWind
With the appropriate login (Server Authentification) should I be able to
create a new database, Create new tables, see permissions and alter them ?
[I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
situation can see but I cannaot do anything else!]
BUT have been told my login will allow this!!!!!
[I have also tried connecting through Query Analyser - no creates allowed]
Jim Bunton
It looks like you have read permissions only
http://sqlservercode.blogspot.com/
"Jim Bunton" wrote:

> SQL Server Personal edition
> Enterprise Manager
> on Win 2000
> I have the above installed on my own computer
> I have connected to a remote database using it's IP adress
> I can see all the tables - eg NorthWind
> With the appropriate login (Server Authentification) should I be able to
> create a new database, Create new tables, see permissions and alter them ?
> [I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
> situation can see but I cannaot do anything else!]
> BUT have been told my login will allow this!!!!!
> [I have also tried connecting through Query Analyser - no creates allowed]
> --
>
> Jim Bunton
>
>