SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrated DB to 2016, having ODBC Access problem


Migrated DB to 2016, having ODBC Access problem

Author
Message
briancampbellmcad
briancampbellmcad
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 565
I was able to get it working by correcting what I was naming in as the Logical Drive files, Data file and Log file. i tried to get the users to try the Access databases and they get an error: SQLState: ‘01000’, SQL Server Error: 53. I tried entering the Port 1433 that I read needed to be open and TCP/IP enabled but the error is still there.
WendellB
WendellB
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5419 Visits: 1927
briancampbellmcad - Monday, February 5, 2018 6:41 AM
I was able to get it working by correcting what I was naming in as the Logical Drive files, Data file and Log file. i tried to get the users to try the Access databases and they get an error: SQLState: ‘01000’, SQL Server Error: 53. I tried entering the Port 1433 that I read needed to be open and TCP/IP enabled but the error is still there.

Sorry to be so late in responding, as have been away for an extended period. It may help to take a look at the system tables in your Access databases - if you specified a version of Access, I missed it. There is a system table called "MSysObjects" that has a field called Connect which contains the ODBC connection string that is used to connect to a particular SQL Server database. You may find some information there that is relevant to your issues.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
briancampbellmcad
briancampbellmcad
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 565
Here's what's in the MSysObjects Connect field: DSN=CPCE_Copy;Description=CPCE_Copy;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=CPCE_DB;
WendellB
WendellB
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5419 Visits: 1927
briancampbellmcad - Tuesday, February 13, 2018 1:02 PM
Here's what's in the MSysObjects Connect field: DSN=CPCE_Copy;Description=CPCE_Copy;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=CPCE_DB;

OK, I think Sue may have deduced the problem. It appears you are using Integrated Security, which means that each network login must be given permissions on a database in order to see it from Access. And Access requires that each user have full read/write permissions for each table. I have seen cases where restoring from a backup did not retain the user permissions. Check the user permissions using SSMS and see what they look like - I suspect they don't have full access to the table objects in SQL Server. I presume from the connect string that you are still using Access 2010. It is possible that there was some change in SQL Server 2016 that changed the way permissions get set, but I'm not aware of any.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
briancampbellmcad
briancampbellmcad
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 565
I'm using or rather allowing Windows Authentication for each user. I'm using Access 2010. I have checked my test user and they have read/write privileges to the database. The user is in the dbo Schema. from SSMS I granted Read, Write, Update, Delete privileges to the user.
briancampbellmcad
briancampbellmcad
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 565
When in Access using the LTM it will take me as far as seeing the system tables for the database but not the data tables.
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36324 Visits: 10377
briancampbellmcad - Tuesday, February 13, 2018 3:16 PM
I'm using or rather allowing Windows Authentication for each user. I'm using Access 2010. I have checked my test user and they have read/write privileges to the database. The user is in the dbo Schema. from SSMS I granted Read, Write, Update, Delete privileges to the user.


Login mappings won't be in the database itself. Logins are for the server and users are for the database. Login mappings indicate what databases the logins have access to. The login allows the connection to the server and then a login is mapped to a user in whichever databases.
In SSMS, go to Security -> Logins. All the people who need to have access should have their logins listed there. Right click on a login, select properties. On the left of the login properties page, click on user mappings.

Sue



briancampbellmcad
briancampbellmcad
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 565
Here's right-clicked on the User on the Security>Logins node:

WendellB
WendellB
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5419 Visits: 1927
It appears that that user is a SQL Server user, not a network user. Your linked tables show Integrated Security, so the user would be trying to connect with their network credentials, not with the "databasetest" account. You can create ODBC connections using standard SQL Server security, but they are frequently an issue when you have multiple users connecting from different workstations.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
briancampbellmcad
briancampbellmcad
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1386 Visits: 565
'databasetest' is a user setup for testing. They have a network login using that name and a password as they are in our active directory. To login to SQL Server, which they can, the SQL Server requires only Windows Authentication. This login can log into SQL Server with no problem and see any table or other object in the database.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search