When using Tablediff I keep getting "Unable to access database"

John Hales (ROI Mailbox) 20

I have SQL 2017 Standard running on Windows 2019 in a VM as a source VM. I also have a 2nd server running SQL 2019 Enterprise running on Windows 2019. I can connect to both and use both using SSMS, including querying any table. I'm using Windows authentication on the 1st server and SQL authentication on the 2nd server. When I use Tablediff utility, I have supplied the source and destination server, database, and table and I have also supplied the destination user and password. Every time I run the command I get "Unable to access database". I confirmed everything is spelled correctly. I have searched online and can't seem to find anything about why it can't connect. Also, I have installed the .NET Framework 3.5 required by Tablediff and can successfully query tables in both server's databases using sqlcmd.

Ronen Ariely 15,096 Reputation points

2023-06-06T03:54:41.2566667+00:00

Hello

(1) Please provide the exact command you use after replacing the sensitive data. Meaning that instead of real database name, user name and password you can use a fake information, but we do need to see the exact format you use in order to understand the issue.

(2) Please try to use the unitality in each database separately to confirm you can use it in one or both databases and which. You can just create two simple tables in ach database and try to compare the two table for each database.

Provide the result of the tests
Erland Sommarskog 101.8K Reputation points MVP

2023-06-06T12:05:54.4133333+00:00

and I have also supplied the destination user and password.

Only on the instance where you are using SQL authentication, I assume?

Also, it is not clear if you are getting the error on both instance, or only one of them.
John Hales (ROI Mailbox) 20 Reputation points

2023-06-06T14:02:26.88+00:00
Here is the exact command (logged in as a user to Windows that belongs to the sysadmin group):

"C:\Program Files\Microsoft SQL Server\140\COM\tablediff.exe" -sourceserver localhost -sourcedatabase test-source -sourcetable SourceTable -destinationserver destsql -destinationdatabase test-dest -destinationtable DestTable -destinationuser sqladmin -destinationpassword sqlpassword

Notes:

I have tried both localhost and the name of the server

The sqlserver user is a DBO on the database

I used SSMS to copy SourceTable to DestTable

Source uses Windows Authentication, destination uses SQL authentication

I get the error on test-source; I have never received any messages about test-dest. I'm not sure if it is because it tries the source first or if it doesn't have a problem connecting to the destination.

Anything else I can provide to help solve this I'll do as quickly as I can. I'm working on a database migration course and want to highlight the tool as a way to find differences between tables that are replicated.

Thanks again for the help!!

Accepted answer

Erland Sommarskog 101.8K Reputation points MVP

2023-06-06T15:36:47.3966667+00:00

Here is the exact command (logged in as a user to Windows that belongs to the sysadmin group):

And you expect you get your sysadmin rights because you are logged into Windows as an administrator? In such case, you should open the command prompt with "Run as Administrator", so that Windows passes the admin token to SQL Server.
Please sign in to rate this answer.

1 person found this answer helpful.
John Hales (ROI Mailbox) 20 Reputation points

2023-06-06T19:19:18.64+00:00

Thanks! That was exactly it. I wish the documentation stated that.
Sign in to comment

When using Tablediff I keep getting "Unable to access database"

0 additional answers