Minimum permission required for SQL Server Migration Assistant for DB2 (DB2ToSQL)

saurabh 356

Hi,

I am trying to use SQL Server Migration Assistant for DB2 (DB2ToSQL) for my migration project.

As part of the project requirements, I need to request for a db2 service account for the migration project. However, the SSMA documentation does not clearly tells what are the list of database privileges required for tool to work.
For obvious reason, it might not be possible to get dbadm,sysadm(or instance id) for migration.

Please advise what is the minimum permission required on DB2 database to migrate schema of all user objects to SQL server.

1 answer

Alexander Ivanov 491 Reputation points

2021-07-14T04:49:54.6+00:00

Hey,

The owner of the schema should have necessary permissions.

Having said that, schema conversion does not require you to connect to production database. Most of the time users prefer to do schema conversion from the test/dev instance, where you will have any level of access you want. Only the data movement would need access to prod database, and for data access that is the authorization - DATAACCESS.

Regards,
Alex.
Please sign in to rate this answer.
saurabh 356 Reputation points

2021-07-14T12:08:26.4+00:00

Hi @Alexander Ivanov ,

Yes, I am trying to connect in Dev only. However, due to org restrictions , I got a read only access. With my access, I can connect to database using Dbeaver tool. However, the same credentials do not work with SSMA. I tried instance id to test the connection and it worked. So now, I am trying to understand what is the least privilege required to work with SSMA. Dataaccess will let the ID do DML, so it might be an issue. Ideally, read access should be good enough. In case SSMA uses some routines, then execute permission might also be required.

Thank you.

Regards,
Saurabh

Alexander Ivanov 491 Reputation points

2021-07-14T15:08:05.077+00:00

Hey,

We don't execute any routines, but we do query a lot of catalog tables and views (SYSTABLES, SYSVIEWS, SYSROUTINES, etc.) in order to discover objects in the database and their SQL definitions. From IBM DB2 docs:

The tables and views in the catalogs are shipped with the SELECT privilege to PUBLIC. This privilege may be revoked and the SELECT privilege granted to individual users.

It is hard to tell what permissions would be needed in your particular environment.

Regards,
Alex.

saurabh 356 Reputation points

2021-07-14T19:18:10.52+00:00

I granted select on all tables in the database. However, still facing issue.

Sharing some additional information.

My source DB2 is at 10.5 and running on windows.

Below is the error message

Connection to DB2 failed.
The user does not have the authority to access the host resource. Check your authentication credentials or contact your system administrator.

Additionally I can access the database and all of its tables using a different sql client DBeaver. DBeaver using a jdbc driver.

The source Db2 in my case is also using server_encrypt, and I am trying to connect using the connection string.

Alexander Ivanov 491 Reputation points

2021-07-21T17:19:00.963+00:00

Hey,

If you enable debug logging (Tools -> Global settings -> Logging), then in the log file you will be able to see the query SSMA executes that fails and troubleshoot further. Based on the error it does seem like it can't even connect to the server, though. How did you create the connection string? What driver/provider are you using? If you are using "Microsoft OLE DB Provider for DB2", then the best way to get the correct connection string in your scenario might be to use the Data Access Tool.

Regards,
Alex.

saurabh 356 Reputation points

2021-07-22T13:18:57.467+00:00

Thanks @Alexander Ivanov .

I am checking the Data Access Tool, and see it expects Package collection, Default schema and Default qualifier. We can keep these values as empty/null. However, the notes say that data access tool will try to create packages(refer attached image). If that the case, then we need to grant the id bind privilege in null collection as well.
Please review and confirm.

Alexander Ivanov 491 Reputation points

2021-07-22T20:49:57.647+00:00

Hey,

I think it is required by the OLE DB provider to create the package(s) when you connect for the first time. AFAIK, this is a one-time operation, so it's supposed to be that your administrator will connect to the DB2 instance once using Microsoft OLE DB provider and users can later do so without the permission. You can also grant permissions to the user, let them connect once and revoke the permissions.

More information on the packages is available under Package Collection section in this article.

Regards,
Alex.

saurabh 356 Reputation points

2021-07-26T16:25:15.2+00:00

Hello @Alexander Ivanov ,

Thank you for your help so far.

I got DBADM authority on my ID, and still getting the same error. I doubt that this issue related to permissions, as I could use the same credentials to access same database from JDBC based database sql clients and even Azure ADF. I could see that Azure ADF connection created a package "MSCS001" under my id.

The error message says "The user does not have the authority to access the host resource, error code: -2147217900". Could it be that SSMA expects user id to have access to underlying OS as well. Doesn't make sense, but the message is a bit misleading as its not giving the resource name.

Below is the error snippet from SSMA logs

Below is the connection string that I am currently using
Provider=DB2OLEDB;User ID=DBUserID;Password=UserPWD;Initial Catalog=DBNAME;Network Transport Library=TCPIP;Network Address=HOSTNAME;Network Port=PORTNUM;DBMS Platform=DB2/NT;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=True;Authentication=Server_Encrypt_UsrPwd;Decimal As Numeric=False;Allow Null Chars=False;LoadBalancing=False;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;Literal Replacement=False;

Alexander Ivanov 491 Reputation points

2021-07-26T19:14:40.963+00:00

Hey,

Are you saying you were able to connect to DB2 instance using this connection string from Data Access Tool, but not from SSMA? Then it might be the access to a certain catalog table or view. Did you have a chance to review the debug log and see if there were any queries that SSMA was executing prior to the error?

SSMA does not require any access to the machine where DB2 is hosted.

Regards,
Alex.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Minimum permission required for SQL Server Migration Assistant for DB2 (DB2ToSQL)

1 answer

Your answer