Migrated DB to 2016, having ODBC Access problem

Question

Migrated DB to 2016, having ODBC Access problem

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
January 29, 2018 at 10:12 am

#77753

Thanks in advance for any clues on this problem. I'm migrating several SQL Server databases from SQL2005 to SQL2016. One database is visible (I can connect to the tables) in a MS-Access frontend, and another database is not though I followed, I think, the same method for each. I retrieved a .bak file and loaded it on the C:\ drive of the host machine and from there I loaded it into SQL2016 using this script and all my tables are there with the expected data. The first line of code displayed that the logical data and log files were named ATCB_DB_Data and ATCB_DB_LOG and I can't seem to change that on my SQL2005 backup.

restore filelistonly from disk = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
     RESTORE DATABASE CPCE_DB
        FROM DISK = 'C:\DATABASES\CPCE_DB_backup_201801290019.bak'
      WITH
        MOVE 'ATCB_DB_Data' TO 'D:\Databases\Data\CPCE_DB_Data.MDF',
        MOVE 'ATCB_DB_LOG' TO 'D:\Databases\Data\CPCE_DB_Log.LDF',
        REPLACE
      ;

The only other issue is when I go to the 'dbo' user it lists sqlAgentProxy rather than my active directory name:

USE [CPCE_DB]
GO
/****** Object: User [dbo] Script Date: 1/29/2018 10:41:36 AM ******/
CREATE USER [dbo] FOR LOGIN [NBCC-US\sqlAgentProxy] WITH DEFAULT_SCHEMA=[dbo]
GO
What I'm seeing in Access is this message:

Viewing 15 posts - 1 through 15 (of 33 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 1

That is a security error. My guess is something had issues when you restored.

That script doesn't restore, so I'm not sure what you did there. That gets meta data for the backup file. If you've restored it and things look OK in SSMS, try adding a new login and user and seeing if that lets you connect.

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

If I'm seeing the 'Logical name' ATCB come up instead of CPCE (the expected logical name) do I have something set incorrectly on my backup process and how can this be fixed?:

MOVE 'ATCB_DB_Data' TO 'D:\Databases\Data\CPCE_DB_Data.MDF' MOVE 'ATCB_DB_LOG' TO 'D:\Databases\Data\CPCE_DB_Log.LDF'

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 3

Nope, logical names in the backup are what's in the db. Perhaps you have the wrong file?

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 4

Steve Jones - SSC Editor - Tuesday, January 30, 2018 1:59 PM
Nope, logical names in the backup are what's in the db. Perhaps you have the wrong file?

It's the correct file as the data is correct, but the naming of the data file and log file are wrong and don't appear to be able to be deleted or edited. I tried another database that did not have this problem and my original problem of not being able to connect to Access persists there too. As to why one Access database connects and 5 others do not is the puzzle. I'm not sure what permissions could be incorrect.

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 5

Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.

The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 6

Steve Jones - SSC Editor - Tuesday, January 30, 2018 3:32 PM
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.
The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

The logical names are from the databases on the source server and they are misnamed... how to rename them has evaded me.

Sue_H SSC Guru Points: 90860 More actions · Answer 7

briancampbellmcad - Tuesday, January 30, 2018 3:46 PM
Steve Jones - SSC Editor - Tuesday, January 30, 2018 3:32 PM
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.
The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.
The logical names are from the databases on the source server and they are misnamed... how to rename them has evaded me.

It's not going to matter at all for restoring. What's in the backup is what you would use for the restore. It is what it is.
After you get it restored, you can rename those logical files. Using t-sql it's just alter database...modify file. You can also do it in Powershell, SMO if you'd rather. There is an article up here that demonstrates each of those:
Changing SQL Server Database Logical Names

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 8

Steve Jones - SSC Editor - Tuesday, January 30, 2018 3:32 PM
Can you go back and verify the names on source databases? The logical name wouldn't change unless you changed it in a source. At least AFAIK.
The permissions thing comes from your error in accessing the object. If the object is there, and you can't access it, then it's usually permissions.

Don't the Users and their permissions copy over also? I've combed through any differences between the SQL Server 2016 migrated database I can see from Access and the one I cannot and I'm not seeing any permissions issues.

After restoring to 2016 from a .bak file I can read all the data from SSMS on each attempt and I've tried 6 different databases. Again one is visible from Access (Linked Table Manager) and 5 are not.

I've tried to locate what is special about the one that consistently succeeds and I cannot find anything. All the users are recreated from the .bak file.

Sue_H SSC Guru Points: 90860 More actions · Answer 9

briancampbellmcad - Wednesday, January 31, 2018 3:16 PM
Don't the Users and their permissions copy over also? I've combed through any differences between the SQL Server 2016 migrated database I can see from Access and the one I cannot and I'm not seeing any permissions issues.
After restoring to 2016 from a .bak file I can read all the data from SSMS on each attempt and I've tried 6 different databases. Again one is visible from Access (Linked Table Manager) and 5 are not.
I've tried to locate what is special about the one that consistently succeeds and I cannot find anything. All the users are recreated from the .bak file.

The users are in the database but the login mappings would not be. You may want to check those also.

Sue

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 10

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 SSCrazy Eights Points: 8678 More actions · Answer 11

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!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 12

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 SSCrazy Eights Points: 8678 More actions · Answer 13

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!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 14

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.