May 17, 2018 at 2:17 pm
I trying to link a database from one server to another but I'm missing something... any ideas on this T-SQL?:EXEC sp_addlinkedserver
@server=N'Badlands',
@srvproduct=N'',
@provider=N'SQLOLEDB.1',
@datasrc=N'Badlands',
@provstr = N'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CPCE_DB;Data Source=NBCCP-SQL01-VM\NBCCSQL01VM',
@catalog = N'CPCE_DB';
May 17, 2018 at 3:29 pm
briancampbellmcad - Thursday, May 17, 2018 2:17 PMI trying to link a database from one server to another but I'm missing something... any ideas on this T-SQL?:EXEC sp_addlinkedserver
@server=N'Badlands',
@srvproduct=N'',
@provider=N'SQLOLEDB.1',
@datasrc=N'Badlands',
@provstr = N'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CPCE_DB;Data Source=NBCCP-SQL01-VM\NBCCSQL01VM',
@catalog = N'CPCE_DB';
To link to another SQL Server, You don't need provstr and you generally want to connect using the SQL Native Client. I have no idea what badlands is but it looks like the server\instance you want to link to is NBCCP-SQL01-VM\NBCCSQL01VM. See if this helps at all:
EXEC master.dbo.sp_addlinkedserver
@server = N'MyLinkedServer', --this is the name of the Linked Server you see under Linked Servers
@srvproduct=N' ', --You don't need to put anything here
@provider=N'SQLNCLI', --Use the SQL naive client
@datasrc=N'NBCCP-SQL01-VM\NBCCSQL01VM', --This is the instance you are connecting to.
@catalog=N'CPCE_DB' ---this is just whatever database you want to connect to.
Sue
May 18, 2018 at 11:05 am
Thanks Sue... This made sense and worked perfectly!
May 18, 2018 at 12:55 pm
One more thing I'm not seeing any tables listed in my linked database.
May 18, 2018 at 1:12 pm
briancampbellmcad - Friday, May 18, 2018 12:55 PMOne more thing I'm not seeing any tables listed in my linked database.
If you tested the connection and that is fine, I'd look next at how the security is setup for that linked server. Does the account have permissions to view the tables?
Sue
May 18, 2018 at 1:56 pm
Sue_H - Friday, May 18, 2018 1:12 PMbriancampbellmcad - Friday, May 18, 2018 12:55 PMOne more thing I'm not seeing any tables listed in my linked database.If you tested the connection and that is fine, I'd look next at how the security is setup for that linked server. Does the account have permissions to view the tables?
Sue
Yes The database will error when i try to open it something like attempt at 'anonymous login'
May 18, 2018 at 2:11 pm
briancampbellmcad - Friday, May 18, 2018 1:56 PMSue_H - Friday, May 18, 2018 1:12 PMbriancampbellmcad - Friday, May 18, 2018 12:55 PMOne more thing I'm not seeing any tables listed in my linked database.If you tested the connection and that is fine, I'd look next at how the security is setup for that linked server. Does the account have permissions to view the tables?
Sue
Yes The database will error when i try to open it something like attempt at 'anonymous login'
I am guessing the error was a login failure for NT AUTHORITY\ANONYMOUS LOGON.
Do you have SPNs set for the SQL Server service accounts?
One of the easier ways to check the SPNs, kerberos is to use the Microsoft Kerberos Configuration Manager for SQL Server. It simplifies things a lot if you aren't used to dealing with it. You can download it from this link:
Microsoft Kerberos Configuration Manager for SQL Server
If you have groups that manage Windows, Active Directory you would want to involved them.
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply