Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ SQL Server Has Started Asking For Login

Posted by: rperkins Sep 10 2019, 07:59 PM

Databases which have been in use over a year are now starting to request SQL Server logins to allow access to the linked tables in Microsoft Access. This breaks some of the linking code/apps and I can't give out the password to the users. My IT guy said MS had recently changed Access to prevent saving of the UID and PW for linked tables as they were saved in plain text. Is he correct or just giving me some grief. If the later, what settings in the SQL database should I discuss with him. If it's an Access problem, what can be done?

Thanks,

Posted by: MadPiet Sep 10 2019, 10:48 PM

Did they recently move the databases or script them out and recreate them? Sounds like the permissions got screwed up or they reinstalled using SQL Server security instead of Windows Authentication (uses Active Directory for User/Group management).

That's my guess anyway... Maybe nvogel knows?

Posted by: nvogel Sep 11 2019, 06:17 AM

Hi rperkins,

You didn't say whether the login still works when you enter the password. If not then maybe SQL Authentication is disabled. In SQL Server Management Studio right-click on the server name, select Properties, select Security from the menu on the left. Is the option "SQL Server and Windows Authentication" selected? If so then check that the login itself hasn't been disabled: SELECT is_disabled FROM SYS.server_principals WHERE name = 'some_login_name';

Assuming logins do work then as far as I know you should be able to save the password in Access. There was an update released yesterday (Version 1908 Build 11929.20300). I installed it and a SQL login still works on my system but it's possible you may have encountered something specific to your environment if you did install any updates.

Your IT guy does have a point however. SQL logins are certainly not considered good practice, they are disabled by default when you install SQL Server and many organisations won't allow them. Windows based authentication (access to databases based on your Windows login) is much more secure and is strongly recommended. Access unfortunately makes the situation worse because it stores the passwords in easily readable unencrypted form. It's not difficult to convert applications to Windows security however.

Posted by: GroverParkGeorge Sep 11 2019, 10:19 AM

"...MS had recently changed Access to prevent saving of the UID and PW for linked tables as they were saved in plain text."

Not true.

I would consider that to be a red herring, as a matter of fact, for something changed in your environment.

While it's true that SQL Logins aren't the preferred approach, it's simply not true that preventing you from using them is a good approach either.

If you CAN change to Windows Authentication, it makes sense to do that. Unfortunately, that doesn't work for remote databases, e.g. a SQL Azure database or one hosted somewhere outside your network.

https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections-2/.

Posted by: AlbertKallal Sep 11 2019, 05:12 PM

Not a change to Access. But perhaps something on your network layout changed.


If the logon works, then you should be able to re-link the tables.
In other words if you enter user + password from the popup prompt, and it works, then you can fix this on your end.


However, during the re-link, you MUST re-create the DSN, since that is the only way to get the ODBC config to re-prompt you to “save” password.

Even better is to delete all the links, and re-link. If you don’t delete the links, then you don’t get this prompt:



So, you can use the linked table manager. Just make sure you check the “prompt for new location”. That will “force” access to re-link, and save the password. This way, you will likely see the ODBC logon prompt – fill it out, and re-fresh.

So, it not clear what changed. If after doing the above, exit access and re-try. If this does NOT work, then you need to delete the links.

Just make sure when you re-link (or re-create) the links, you use a FILE dsn. This will cause access to re-link using a DSN-less connection, and thus once done, you can re-distribute the front end and not have to change or re-configure the workstations for each front end you deploy.

Fingers crossed the re-link works. If a re-link does not work, then you have to delete all the links, and start over – ensuring that to “check” the above box to save the password.

So, this is fixable on your end.

Once re-linked, then you can re-deploy the front end to workstation as a result.

Regards
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada