none
Create Linked Server within SQL Server 2016 into DB2 ? RRS feed

  • Question

  • Hello believe this is possible but what are the requirements and steps do this and to also ensure only a particular SQL Login/User name can access the Linked Server into DB2 and not all Logins within SQL Server 2016?

    Here are the current list of Providers that shows up within my SQL Server 2016 Linked Server options:

    (Was hoping my above pasted image of my current Linked Server Provider list would show up but if didn't there are 8 listed within drop down box and none of them refers to "DB2".  Didn't want to time consume manually typing out all 8 Provider lists exact spellings.)

    Am wondering do I need to download and install some particular item that refers to DB2/some other configurations or can one of the above accomplish it?

    Thanks in advance.


    • Edited by techresearch7777777 Thursday, June 25, 2020 6:11 PM Pasted image appears to not show.
    Thursday, June 25, 2020 5:55 PM

All replies

  • Ok I manually typed out each Provider within my Linked Server drop down box.

    Here is what is currently listed for me:

    Microsoft OLE DB Provider for SQL Server
    SQL Server Native Client 11.0
    OLE DB Provider for Microsoft Directory Services
    Microsoft OLE DB Provider for Search
    OLE DB Provider for SQL Server Integration Services
    Microsoft OLE DB Provider for ODBC Drivers
    Microsoft OLE DB Provider for Analysis Services 14.0
    Microsoft OLE DB Simple Provider

    Thanks in advance.

    Thursday, June 25, 2020 7:19 PM
  • Hello believe this is possible but what are the requirements and steps do this and to also ensure only a particular SQL Login/User name can access the Linked Server into DB2 and not all Logins within SQL Server 2016?

    First you need to get hold of an OLE DB provider for DB2. This does not ship with SQL Server, as an OLE DB provider is a general API which can be used by many type of clients, not just only SQL Server. No, I don't know the name od or download link on the top of my head, but Google can surely help you.

    As when it comes to controlling access, DB2 is no different from any other remote data source. The linked server as such is not a securable, so any one can access the linked server. But to have any use for it, they need mapping to a valid login on the remote data source. The default is self-mapping, that is, the same credentials you used to log on to SQL Server are attempted on the remote data source. This is popular when the linked server is SQL Server, but I doubt that it works with DB2.

    The alternative is to set up login mapping to a username/password on the remote data source. This can be set up to apply to everyone (not a very good idea), or only to specific users. But these has to be explicit logins - you can set up mapping for role or an AD group. Different logins can have different mapping.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by TiborKMVP Friday, June 26, 2020 12:18 PM
    Thursday, June 25, 2020 9:26 PM
  • Hi techresearch7777777,

    >>>Hello believe this is possible but what are the requirements and steps do this and to also ensure only a particular SQL Login/User name can access the Linked Server into DB2 and not all Logins within SQL Server 2016?

    You need to download and install Microsoft OLDB Provider for DB2.

    Then check following posts:
    SQL Server: link DB2 table

    Linked server permission for specific user in SQL Server

    MS-SQL DB2 Linked Server

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 26, 2020 3:07 AM
  • Thanks for all of the very helpful replies, much appreciated.

    Referring to this site's instructions:   https://www.mssqltips.com/sqlservertip/2151/how-to-create-a-sql-server-link-server-to-ibm-db2/

    The connection string example that's listed is:

    Provider=DB2OLEDB;User ID=db2user;Password=mypassword;Initial Catalog=mydatabase;Network Transport Library=TCP; Host CCSID=37;PC Code Page=1208;Network Address=192.168.9.65;Network Port=446;Package Collection=MSDB2COL; Process Binary as Character=False;Connect Timeout=15;Units of Work=RUW;DBMS Platform=DB2/MVS;Defer Prepare=False; Rowset Cache Size=0;Max Pool Size=100;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;

    What is the "Package Collection" setting and how do I know what mine should be?

    Just wondering what other settings I need to tweak other than the main ones for a basic connection like:

    - User ID
    - Password
    - Initial Catalog
    - Network Address
    - Port

    Thanks.

    Tuesday, June 30, 2020 5:28 PM
  • What is the "Package Collection" setting and how do I know what mine should be?

    You ask someone who knows DB2. This is a forum SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 30, 2020 9:16 PM
  • Hi techresearch7777777,

    >>>What is the "Package Collection" setting and how do I know what mine should be?

    Please refer to following article:

    https://docs.microsoft.com/en-us/host-integration-server/core/data-links-db2-2
    or
    https://flylib.com/books/en/2.899.1.78/1/

    If you are unsure please consult your DB2 DBA.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 1, 2020 1:40 AM