Microsoft SQL Server 2008 R2 Unleashed- P230:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 2254 CHAPTER 54 Managing Linked and Remote Servers TIP When a linked server is created using sp_addlinkedserver a default self-mapping is added for all local logins. This means that for non-SQL Server providers SQL Server authenticated logins may be able to gain access to the provider under the SQL Server service account. If you want you can issue the sp_droplinkedsrvlogin command to remove these mappings. Figure depicts the overall technical architecture of what is being enabled via linked servers and providers. There is a SQL Server 2008 side set of provider DLLs along with a client-side provider that communicates directly with the data sources. Clients usually connect to SQL Server 2008 via the native SQLNCLI provider OLE DB or via the ODBC provider. OLE DB Provider for MS SQL Server UcfOSOit MS OLE DB Provider ODBC Afcrtsoft SQL Server 2008 OLE DB Provider for Jet OLE DB Provider for Oracle SQL Server - 2008 Sybase Informix DB2 Others OLE DB Provider for ODBC file Excel spreadsheet JET database FoxPro Database Dbase file Access Database ORACLE OLE DB Provider for Indexing Service Active Directory Services Index Server Others Client Tier Server Tier Remote Data Source Tier all can be Linked to SQL Server FIGURE Linked servers provider architecture. This provider architecture allows data sources to be accessed from within SQL Server 2005 with the highest degree of efficiency and integrity possible. Some of the arguments for sp_addlinkedserver are needed only for certain OLE DB providers. Because of the number of different options and settings available you should Adding Dropping and Configuring Linked Servers 2255 always double-check the documentation for the OLE DB provider to determine exactly which arguments must be provided and what strings are to be specified. Following is the sp_addlinkedserver procedure sp_addlinkedserver server server srvproduct product_name provider provider_name datasrc data_source location location provstr provider_string
