SSMA creates a trusted connection to my remote SQL. Opening Access requires unchecking and reconnecting
-
Sunday, May 06, 2012 2:50 PM
I've run SSMA and it successfully creates linked tables from Access to my internet remote SQL 2008 Express (which is on the same computer, but I'm using no-ip and my router and not my LAN to route my server name rnsdb.no-ip.info to SQL. rnsdb.no-ip.info or the IP connects me to SQL) The database tales show on SQL. Since it's an internet remote connection for many disparate users and not a LAN/WAN, I can't use Windows Authentication
However, even though I chose Server Authentication during the SSMA conversion, when I launch Access, and open one of the tables, it can't connect because it appears to be using a trusted location connection string. Message is "the login is from an untrusted location and cannot be used with Windows Authorization". When I click OK, a new login window appears with the trusted connection box checked. If I uncheck it and enter the server name, userid, and password, I successfully log in to the SQL database, and Access functions fine as a front end with the tables stored on SQL. Just like it's supposed to.
The connection string showing when the mouse hovers over the linked table name in Access is
OBDC;;Driver=SQL Server; Server=rnsdb.no-ip.info;APP=SSMA;Database=zztest1;table=dbo.name1
I can't figure out how SSMA generates this string, where it's stored, how to change it, or why there isn't Trusted_Connection=false in the connection string. I can get my users to go through this 2 step error login but its really clumsy.
Anyone else encounter this and figured it out?
Ron
All Replies
-
Monday, May 07, 2012 12:22 AM
I cleared out everything and reran SSMA. Same result, except now in the connection string is "network=DBMSSOCN"
I'm not sure what's changed from a couple of days ago. I did fiddle around using Administrative Tools to create some ODBC connections called SQL Server, but since I didn't know exactly what I was doing, I'm not sure if this caused it. I've also just disabled named pipes which had been enabled. Apparently these aren't necessary to connect to SQL
Ron
-
Tuesday, May 08, 2012 6:10 AMModerator
Hi Ron,
Could you please describe more detailed that how did you get the error?
According to your description, I got that after you open your SSMA, you connect to the SQL Server first and then add access database. Does that error message pop up when you are adding the access database?
Please also refer to this white paper:
Optimizing Microsoft Office Access Applications Linked to SQL Server
Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help. -
Tuesday, May 08, 2012 8:28 AM
No, the error comes after SSMA has successfully completed its upload and link of tables to my SQL server using server authentication. It comes when I then launch Access and go to edit one of the tables. Access apparently tries to login using Windows authentication, apparently because there's nothing in the connection string to tell it otherwise
Ron
- Edited by cove3 Tuesday, May 08, 2012 8:39 AM
-
Tuesday, May 08, 2012 9:13 AMModerator
Hi Ron,
Please see this kb article: ACC: Linked table to SQL Server does not prompt for SQL login / password as expected
Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.- Proposed As Answer by yaphets Monday, May 14, 2012 1:28 AM
- Marked As Answer by Iric WenModerator Monday, May 14, 2012 8:00 AM
-
Saturday, May 12, 2012 7:43 PM
find your solution on this link, it may help you.
http://www.austinsqlserver.com/sqlserver072011.aspx
.png)
