X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> SQL Server Has Started Asking For Login, SQL Server 2012    
post Sep 10 2019, 07:59 PM

Posts: 4
Joined: 20-August 07

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?

Go to the top of the page
post Sep 10 2019, 10:48 PM

Posts: 3,465
Joined: 27-February 09

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?
Go to the top of the page
post Sep 11 2019, 06:17 AM

Posts: 1,061
Joined: 26-January 14
From: London, UK

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.
Go to the top of the page
post Sep 11 2019, 10:19 AM

UA Admin
Posts: 36,747
Joined: 20-June 02
From: Newcastle, WA

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

Check out this article.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Sep 11 2019, 05:12 PM

UtterAccess VIP
Posts: 2,953
Joined: 12-April 07
From: Edmonton, Alberta Canada

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.

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

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th February 2020 - 04:48 AM