bergstpg
asked on
How do you create a Linked Server using Trusted Connection
How do you create a Linked Server using Trusted Connection? Is it something like:
sp_addlinkedserver @server = 'servername'
, @srvproduct = 'SQL Server'
, @provider = 'SQLOLEDB'
--[ , [ @datasrc = ] 'data_source' ]
--[ , [ @location = ] 'location' ]
, @provstr = 'Trusted_Connection=yes;In tegrated Security=SSPI;Data Source=servername;Initial Catalog=master'
sp_addlinkedserver @server = 'servername'
, @srvproduct = 'SQL Server'
, @provider = 'SQLOLEDB'
--[ , [ @datasrc = ] 'data_source' ]
--[ , [ @location = ] 'location' ]
, @provstr = 'Trusted_Connection=yes;In
ASKER
Unfortunately I get the following error message using your syntax for sp_addlinkedserver - I changed the servername to my servername:
Server: Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL Server'.
To be more clear, I don't want to create a SQL Server login - just use Windows Authentication.
Server: Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'SQL Server'.
To be more clear, I don't want to create a SQL Server login - just use Windows Authentication.
ASKER
What I would really like is to use the OPENROWSET instead of a linked server to connect to a remote server, but all the references I have found on this have said it isn't possible. It seemed like it might have been in the past:
http://support.microsoft.com/default.aspx?scid=kb;en-us;256052&sd=tech
http://support.microsoft.com/default.aspx?scid=kb;en-us;256052&sd=tech
is this SQL 2000 or 2005?
generally you can do
EXEC sp_addlinkedsrvlogin 'ServerName', 'true'
This will use the connected user login to login to the remote server
generally you can do
EXEC sp_addlinkedsrvlogin 'ServerName', 'true'
This will use the connected user login to login to the remote server
ASKER
SQL Server 2000
I created a linked server using the following syntax:
EXEC sp_addlinkedserver 'servername','sql server'
EXEC sp_addlinkedsrvlogin 'servername', 'true'
I got the error Login not associated with a trusted connection when I tried to view the tables within EM. I have sa access to this server.
I then tried the following sytax:
EXEC sp_addlinkedserver 'servername','','sqloledb'
EXEC sp_addlinkedsrvlogin 'servername', 'true'
This showed me tables, but they where the tables in the master database of the local server.
Finally I tried the following sytax:
EXEC sp_addlinkedserver 'servername','','sqloledb' ,'dbname'
EXEC sp_addlinkedsrvlogin 'servername', 'true'
This time when I accessed the tables I got the error Error 6: specified sql server not found. Both the client and server are using tcpip only in the client and server config utilites.
I am still looking for a solution. Thx.
I created a linked server using the following syntax:
EXEC sp_addlinkedserver 'servername','sql server'
EXEC sp_addlinkedsrvlogin 'servername', 'true'
I got the error Login not associated with a trusted connection when I tried to view the tables within EM. I have sa access to this server.
I then tried the following sytax:
EXEC sp_addlinkedserver 'servername','','sqloledb'
EXEC sp_addlinkedsrvlogin 'servername', 'true'
This showed me tables, but they where the tables in the master database of the local server.
Finally I tried the following sytax:
EXEC sp_addlinkedserver 'servername','','sqloledb'
EXEC sp_addlinkedsrvlogin 'servername', 'true'
This time when I accessed the tables I got the error Error 6: specified sql server not found. Both the client and server are using tcpip only in the client and server config utilites.
I am still looking for a solution. Thx.
ASKER
New points
use the one where you could see the data in Master database... then fire this query
SELECT * FROM <Servername>.<DatabaseName >.<userNam e>.<tableN ame>
and that should work
SELECT * FROM <Servername>.<DatabaseName
and that should work
ASKER
Sorry, still doesn't work
Tried this:
SELECT top 100 * FROM dsm02415.express.dbo.compu ter
Got this:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'dsm02415' does not contain table '"express"."dbo"."computer "'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='dsm02415', TableName='"express"."dbo" ."computer "'].
I tripled checked the table's existance and I have SA. Any other ideas?
Tried this:
SELECT top 100 * FROM dsm02415.express.dbo.compu
Got this:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'dsm02415' does not contain table '"express"."dbo"."computer
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='dsm02415', TableName='"express"."dbo"
I tripled checked the table's existance and I have SA. Any other ideas?
add login that you plan to use on both sql servers (make sure they have all needed rights)
then create linked server:
--servername must be your destination sql server name:
USE [master]
GO
EXEC master.dbo.sp_addlinkedser ver @server = N'servername', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrv login @rmtsrvname = N'servername', @locallogin = NULL , @useself = N'True'
GO
then create linked server:
--servername must be your destination sql server name:
USE [master]
GO
EXEC master.dbo.sp_addlinkedser
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
EXEC master.dbo.sp_serveroption
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrv
GO
Double check that SA has a user mapped (dbo or others) in the database you are trying to access (Express)
This problem can ONLY happen if you do not have rights or the object does not exist, since the object exists, the user does not have rights.. have seen it over a million time.
This problem can ONLY happen if you do not have rights or the object does not exist, since the object exists, the user does not have rights.. have seen it over a million time.
clarification:
--add login
NT login - such as yourdomain\yourusername
--add login
NT login - such as yourdomain\yourusername
ASKER
EugeneZ
I ran your script - adjusted to my environment - and specifically added my domain login to both servers - sa on connected server, dbo in db of destination server - and I get the following error:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am beginning to think this isn't possible. Has anybody been able to do this? I have tried about every possible solution without success.
I ran your script - adjusted to my environment - and specifically added my domain login to both servers - sa on connected server, dbo in db of destination server - and I get the following error:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am beginning to think this isn't possible. Has anybody been able to do this? I have tried about every possible solution without success.
ASKER
Einstine98
I tried connecting to another db that the mapped dbo was sa - still no luck - same errors as seen above.
I tried connecting to another db that the mapped dbo was sa - still no luck - same errors as seen above.
make sure you have fresh SP of sql servers:
check:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=201557
check:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=201557
it is possible.
Can you see your NT login (domain\yourlogin) among sql server logins on both servers?
And you connected to sql server in EM with windows authotication?
and you open QA with windows authotication?
And you run
select * from yourlinkedserver.master.db o.sysobjec ts?
And get error?
----
What OS do you have where the sql server installed:
if it is windows 2003:
check MS DTC settings:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
Can you see your NT login (domain\yourlogin) among sql server logins on both servers?
And you connected to sql server in EM with windows authotication?
and you open QA with windows authotication?
And you run
select * from yourlinkedserver.master.db
And get error?
----
What OS do you have where the sql server installed:
if it is windows 2003:
check MS DTC settings:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
ASKER
Not sp4 and its fixes, but sp3a and its fixes. 8.00.818
I don't have access to modify Active Directory, nor do I want that responsibility. If this is the problem, I am abandoning this question.
I don't have access to modify Active Directory, nor do I want that responsibility. If this is the problem, I am abandoning this question.
sp3a and its fixes. 8.00.818 - is good:
what is Windows OS do you have on the servers?
--
It is not Active Directory issue
--
When you open logins in Sql server Enterprice Manager for the 2 sql servers -
can you see your NT login ?
what is Windows OS do you have on the servers?
--
It is not Active Directory issue
--
When you open logins in Sql server Enterprice Manager for the 2 sql servers -
can you see your NT login ?
ASKER
MS DTC is setup correctly on both servers.
No, because I am part of a domain group.
Earlier I tried adding my login manually to both servers - as seen above - without success.
No, because I am part of a domain group.
Earlier I tried adding my login manually to both servers - as seen above - without success.
ASKER
Windows 2003 - with and without SP1
<Earlier I tried adding my login manually to both servers - as seen above - without success.
?
Can you access the QA of the sql server, As let say, 'sa':
and run (it will add your NT login to sql server if it is not there and add ro 'sa' role)?
exec sp_grantlogin N'yourdomain\yourlogin'
exec sp_defaultdb N'yourdomain\yourlogin', N'master'
exec sp_addsrvrolemember N'yourdomain\yourlogin', sysadmin
--please post a result
?
Can you access the QA of the sql server, As let say, 'sa':
and run (it will add your NT login to sql server if it is not there and add ro 'sa' role)?
exec sp_grantlogin N'yourdomain\yourlogin'
exec sp_defaultdb N'yourdomain\yourlogin', N'master'
exec sp_addsrvrolemember N'yourdomain\yourlogin', sysadmin
--please post a result
ASKER
Yeah - I have SA access, thus I can use NT Authentication to connect the SQL Server using QA. Our group of 11 support 6000 dbs on 300 servers using Windows NT Authentication. I would expect us to have other problems with Windows NT Authentication than this one. Again, I am not a believer at this point.
did you or did not run (let say on 2 test servers):
exec sp_grantlogin N'yourdomain\yourlogin'
exec sp_defaultdb N'yourdomain\yourlogin', N'master'
exec sp_addsrvrolemember N'yourdomain\yourlogin', sysadmin
--
and what an error message (if there is any) did you get after connecting each other through linked server connection (created as per posts above) ...?
exec sp_grantlogin N'yourdomain\yourlogin'
exec sp_defaultdb N'yourdomain\yourlogin', N'master'
exec sp_addsrvrolemember N'yourdomain\yourlogin', sysadmin
--
and what an error message (if there is any) did you get after connecting each other through linked server connection (created as per posts above) ...?
plan B (no linked server for sql server 2000 with SP3 and up):
select * from OPENDATASOURCE('SQLOLEDB', 'Data Source=yoursqlserver;Initi alCatalog= master;Int egrated Security=SSPI').master.dbo .sysobject s
select * from OPENDATASOURCE('SQLOLEDB',
ASKER
Result:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
[OLE/DB provider returned message: Invalid connection string attribute]
Been here done this. OPENROWSET doesn't work either.
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
[OLE/DB provider returned message: Invalid connection string attribute]
Been here done this. OPENROWSET doesn't work either.
can you add your NT login to the destination sql server logins?
ASKER
I can, but I have already tested this. Is dbo ok to test - even though I have effective SA rights? If I give myself sa it causes alarms to occur that I want to avoid.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EXEC sp_addlinkedserver @server='servername', @srvproduct='SQL Server',
@provider='SQLOLEDB'
Now the trusted connection comes into picture, when you create the login
This example creates a mapping to ensure that only the Windows NT user Domain\Mary connects through to the linked server Accounts using the login MaryP and password NewPassword.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'NewPassword'