Migrated DB to 2016, having ODBC Access problem

Question

Migrated DB to 2016, having ODBC Access problem

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 1

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 SSC Guru Points: 90860 More actions · Answer 2

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

Here's right-clicked on the User on the Security>Logins node:

WendellB SSCrazy Eights Points: 8678 More actions · Answer 4

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!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 5

'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.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

briancampbellmcad - Wednesday, February 14, 2018 2:41 PM
'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.

You have three different threads for basically the same issues. I thought you created this test account when you started having the issues? In that case it's likely limited in understanding some of the issues.

In one of your posts in one of the threads, someone suggested having the users try with SSMS. You said you installed in on their computer and had to add the user to the database users in order for SSMS to work. So if that's the case and that was one of the original users of this then security is a part of the issue. But If you just restored the database to another server though, the users would be in the database but not the logins. And if they used to connect another way such as using SQL authentication, that creates a different issue. There is quite a bit there that isn't adding up.

In another post you mentioned that all the users could connect with SSMS and see everything in the database but not using the Access app. The only difference between those two would be how they connect - so then the DSN. And in another one about the DSN and someone asking about doing this with the registry key, I mentioned that there is an additional entry in a different key that would be needed. So it seems there are one or more issues with the DSN being used.

You had mentioned the server name and it's a named instance. Using 1433. If you have other instances using that same port then it's a crap shoot on which one you connect to. I think it's usually the first instance installed but not always. If you are using dynamic ports for the instance, try a static port.
For the DSN, you really should consider using the ODBC Data Source administrator just for the ability to test the DSN.
The accounts accessing the database with the access app need to have logins with their windows account since you are specifying trusted connections in the DSN.
You can check the mappings for logins by executing sp_msloginmappings in the master database. You can specify a login using something like:
exec sp_msloginmappings 'DomainName\LoginName'
If you don't specify a login, it maps all logins.
All of those logins that are using the access app need to be mapped to the database with the necessarily permissions.

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 7

I ran this: exec sp_msloginmappings 'NBC-US\databasetest' and exec sp_msloginmappings 'NBC-US\campbell' both of which list myself 'campbell' and 'databasetest' as usernames mapped to DBName CPCE_DB. What additional information can I provide to clarify the situation? Both users have all the same permissions in SQL Server.

Sue_H SSC Guru Points: 90860 More actions · Answer 8

briancampbellmcad - Thursday, February 15, 2018 10:12 AM
I ran this: exec sp_msloginmappings 'NBC-US\databasetest' and exec sp_msloginmappings 'NBC-US\campbell' both of which list myself 'campbell' and 'databasetest' as usernames mapped to DBName CPCE_DB. What additional information can I provide to clarify the situation? Both users have all the same permissions in SQL Server.

The other users. If you set up a test account, that doesn't matter. Not your account, not the test account but an account from the previous database that you have not touch at all since migrating the database.

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 9

The database in its previous instance on SQL 2005 did not have the only user of the database CPCE_DB, Ms. Hall, nor any other users except Guest, Dbo, and Sys. I created her in the new database and was never able to get her into the new one through Access. So I created a test VM and a new 'user' called 'databasetest' so I could emulate her environment on a VM without the constant emails to her of "can you get in yet?" I just can't figure out from that VM why a local SSMS on that machine can see the SQL tables but can't through Access.

Sue_H SSC Guru Points: 90860 More actions · Answer 10

briancampbellmcad - Thursday, February 15, 2018 11:44 AM
The database in its previous instance on SQL 2005 did not have the only user of the database CPCE_DB, Ms. Hall, nor any other users except Guest, Dbo, and Sys. I created her in the new database and was never able to get her into the new one through Access. So I created a test VM and a new 'user' called 'databasetest' so I could emulate her environment on a VM without the constant emails to her of "can you get in yet?" I just can't figure out from that VM why a local SSMS on that machine can see the SQL tables but can't through Access.

The users got in someway so if you are using Windows authentication and the people using the program accessed the database but aren't users in the database and are not logins on the server....something is missing from from that.
If you want to use the testuser you created and try to get in the database using Access and a DSN, create the DSN using the ODBC Data Source Administrator. That's been mentioned a few times in these threads. When you create the DSN using the ODBC Data Source Administrator you can test it.

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 11

You mean the ODBC from control panel as opposed to that in Access? ~ "Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)"

Sue_H SSC Guru Points: 90860 More actions · Answer 12

briancampbellmcad - Thursday, February 15, 2018 12:25 PM
"Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)"

Yes that is the ODBC Data Source Administrator. You can't test it with reg files.

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 13

System or User DSN... and all that does is connects to my SQL Instance right? How do i get to the databases inside it?

Sue_H SSC Guru Points: 90860 More actions · Answer 14

briancampbellmcad - Thursday, February 15, 2018 1:10 PM
System or User DSN... and all that does is connects to my SQL Instance right? How do i get to the databases inside it?

User DSN. And make sure to add your Windows account to the instance with the migrated database. And all it does is connect to the instance on the test.
Don't worry about anything other than creating it and testing it. That part needs to be correct before you can do anything with the registry.

After you get one that works, you can use that for the export and you are also missing one other addition to the registry as I mentioned earlier. So just the routine being used won't work either. The DSNs are also listed in \HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources in addition to the DSN definition in
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
You can see a screenshot of this in the following:
Export an ODBC Data Source from the registry

You can follow the same once you get a DSN to work on your PC and have the entries correct.

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 15

I must not have followed something correctly. I Created a DSN called CPCE_2016_Copy on my machine, found that file and in regedit copied it over to the registry on the VM. But i can't find it in the VM's registry. I did it twice and failed twice.