Understanding ODBC bridge Authentication

In an MS Access database I have tables linked from SQL Server via an ODBC bridge. The bridge is set up with SQL Authentication. However when I link a table to this Access database I can use either the SQL user name and password or Trusted Connection. Either way the table is accessible. The connection strings are below for the same table. Is this normal ? If the bridge is set up with SQL Authentication I thought that is the way the SQL tables had to be linked. Trying to understand how this works so I can set up other clients. Thanks.

ODBC;DSN=ERP_Data;Description=ERP Database;Trusted_Connection=Yes;APP=Microsoft Office 2016;;TABLE=dbq.ds_WrkCtr

ODBC;DSN=ERP_Data;Description=ERP Database;UID=ERP_dbq;;APP=Microsoft Office 2016;;TABLE=dbq.ds_WrkCtr
StampITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not sure what you mean by "bridge" if you just mean a ODBC connection, OK.

With Access and a ODBC table, it will first look in the connection string of the linked table for info it needs.   Part of that can be a DSN.

 Aside from that, you can use SQL Authentication (a user name/password) or Trusted connections, or a combination of both.   Which you use depends on SQL's settings.   It may allow one or the other, or both.

Trusted connections is generally better I find, but there is no right or wrong here; it depends on the situation and what you have to work with.  For example if your working across multiple domains, then trusted connections are out unless there is a trust setup between them.

 What you should try and do with your app is avoid the use of DSN's.   Put everything needed in the connection string.  Also provide a means in the app for updating the server name and database it is pointing to.

Jim.
0
StampITAuthor Commented:
Yes. I mean ODBC connection.

Our SQL Server is set up to accept SQL Server and Windows Authentication. There is just one domain.

I agree Trusted Connections are better.

Are you suggesting not using the Data Source Administrator and instead put everything needed into a connection string ? I am not sure how to do that or provide a way to update the server name and database.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Are you suggesting not using the Data Source Administrator and instead put everything needed into a connection string ?>>

 Yes.  Installing DSN's on an end users machine can be difficult and best if avoided altogether.

<< I am not sure how to do that or provide a way to update the server name and database.>>

  You would run through the tableDefs collection and modify the .Connect property (which is a string) for any ODBC table.

 See the following, which will help convert what you have and run through a lot of the details about what your asking:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

 also this has some code which you should find helpful (shows how to create a linked table from scratch in code):

https://support.microsoft.com/en-us/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in

 Again, it's just adding a tabledef object.

 Bounce back with any questions on the above.

Jim.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeCommented:
Another option would be to read my article on A more efficient Linked Table Manager, download and install the application.  This Access add-in will help you create DSN less connections to your SQL database, and can make it easy to update these (for moving from a development database to a production database).
0
StampITAuthor Commented:
Jim,

Trying to replicate the "AttachDSNLessTable" function as a way to educate myself on how this works. So far I am getting an "invalid argument" error when I try to run the function from the Immediate window. Does the table need to exist in the database or does the function add it ?  New to this so not sure how it all goes together. Thanks.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Note: It is a common misunderstanding that you can provide authentication with the DSN definition in ODBC Administrator. You always have to add login info on connect (but Trusted Connection being the default).
Authentication in the ODBC Administrator is just used for retrieving and pre-occupying default database and further settings, and has no meaning beyond.
0
Dale FyeCommented:
I'll say it again, use my Linked Table Manager (LTM) for Access and SQL Server, it is extremely easy to install and eliminates all of the headaches. of writing the code yourself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StampITAuthor Commented:
Dale,

I would like to give this a try.

How do I know whether my Access version is 32 or 64 bit ? Assuming 64 but do not know for sure.  I am using Office 16.

Where is %AppData\Roaming\Microsoft\AddIns\ ?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It is %AppData%\Roaming\Microsoft\AddIns\ , and you can put that into e.g. Explorer to see where it points to.

On a 64bit OS you can (usually) just look for the binary location - is it in "Programs", then it is 64bit, in "Programs (x86)" it is 32bit (in most cases).
0
Dale FyeCommented:
generally, most people have the 32 bit version of Office.  
Office versionEvery version of Office seems to change this, in some versions you can get to this screen via "File => Help", in others it is buried in the File => Options => Resources => About.  I don't have 2016 installed anywhere, but in 2013, when you open an Access database you will see your name in the upper right corner with a drop down button. Click the dropdown and select account settings.

that path should read:

%appdata%\Roaming\Microsoft\addins\

because different versions of windows put that folder in different places.  Using  %appdata% causes Access (probably windows) to make the appropriate determination of the actual path to that folder.

Did you copy that from my article?
0
StampITAuthor Commented:
Dale - Yes. Copied from your article.
0
StampITAuthor Commented:
I was able to download and install the Add In. It appears to work. I was able to link a table from SQL Server using the Add In. What is the process to create a DSN-less link ? I have several Access databases that contain linked SQL tables. They are opened by the user via Access runtime. Thanks.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Trying to replicate the "AttachDSNLessTable" function as a way to educate myself on how this works. So far I am getting an "invalid argument"

 This functions creates a brand new table.   make sure when you copy/paste that code, you are scrolling of to the right.  The full declare is:

Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean

  Username and password are optional, but server, database (in SQL) at a minimum must be supplied.

  Dale's got a great utility BTW, but you still might want to play around with the code in the articles I posted so you understand how it works.

Jim.
0
StampITAuthor Commented:
Jim - I agree. I believe the issue with the function is in the "Set td =" statement. It doesn't like dbAttachSavePWD. I do not see where that is defined anywhere. Any ideas ? Thanks.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
dbAttachSavePWD

 It is an attribute for the tabledef.  Not sure I understand why it's a problem...does your app compile.   if so, what references do you have?

Jim.
0
StampITAuthor Commented:
It does compile. Do not know what you mean by "What references do you have?"
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In the VBA editor, Tools, then references.

Jim.
0
StampITAuthor Commented:
Visual Basic For Applications, Microsoft Access 16.0 Object Library, OLE Automation, and Microsoft DAO 3.6 Object Library.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You should not have DAO in the list.  Instead, you should have "Microsoft Office 16.0 Access database engine object library".

 Still the DAO reference should have worked.  That constant has been around for ages.

 But try the ACE version and see if that takes care of it.

Jim.
0
StampITAuthor Commented:
For References I have Visual Basic For Applications, Microsoft 16.0 Object Library, and OLE Automation checked.

In the immediate window used  ?AttachDSNLessTable("Pot_Today", "dbo.Pot_Today", "servername", "databasename","","") to execute the code. Received a "Compile error: User defined type not defined" message.

What is the "ACE version"

Can I use the utility created by Dale to create a DSN-less connection ?

Thanks
0
Dale FyeCommented:
Yes, all of the connections you create to SQL Server tables using my application will be DSN-less.

But as I said before, you will have to do that from a computer on the clients network which as Access installed.  As it is, you cannot run the code from a run-time application.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<What is the "ACE version">>

"Microsoft Office 16.0 Access database engine object library".

 Still though it should not make a difference.  That constant is in both libs.

 If you want to post a small sample DB where it fails, I'll figure out what's going on.

Jim.
0
StampITAuthor Commented:
Jim,

Have attached a small database. Appreciate your help. Thanks.
DSN_Less_EE.accdb
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So in the DB you posted, you hadn't set a reference for the ACE Engine lib.   Here's what it should look like:

Region-Capture.jpg
 Your db did not initially compile, but after adding the reference it did.  Also note the debug window, which now shows that a value is seen for dbAttachSavePWD.

Also if you hit F2 (or click the object browser button) and then select the drop down for the library and select "DAO", which is the lib for this, you will see the constant as part of that:

Region-Capture.jpg
 Note the lib reference at the bottom.

 As I said, this should have worked with the DAO 3.6 reference you had set earlier as well.   But the ACE reference is really the one you want.

Jim.
0
StampITAuthor Commented:
Jim,

Still get the invalid argument error. See attached Word doc. Have the references set up correctly, I think. However cannot see anything DAO in the object browser.

By the way how do you copy and paste images into the body of the comment. I cannot so I attach files.

Thanks.
EE_References_DAO.docx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Wrong drop down in the object browser.  It's the top one that selects the lib.   But in any case, you should be able to compile without error, no?

Jim.
0
StampITAuthor Commented:
Yes. It compiles OK.

OK. Using correct drop down I see the DAO and dbAttachSavePWD attribute.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<OK. Using correct drop down I see the DAO and dbAttachSavePWD attribute.>>

 and your still getting an error?   I must  not be understanding something.

Jim.
0
StampITAuthor Commented:
Yes still getting "AttachDSNLessTable encountered an unexpected error: Invalid argument".

Using the following in the Immediate window:
?AttachDSNLessTable("dbo_Pot_today","dbo.Pot_today","SqlServer","ERPDatabase","","")
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There is a bug in that code.  The declare is:

Function AttachDSNLessTable(stLocalTableName As String, stRemotTableName As String, stServer As String, _
            stDatabase As String, Optional stUsername, Optional stPassword As String)

when it should be:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, _
            stDatabase As String, Optional stUsername, Optional stPassword As String)

'e' is missing in remote table name, so it ends up blank when you execute the CreateTable().

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, this is always why you want to use 'Option Explicit'

Jim.
0
StampITAuthor Commented:
Jim - Finally the code created the linked table. Thanks for all your work. Helped me understand how this all works. Where is Option Explicit ? and what does it do ?

Dale - Your utility works great. Thanks for the help.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Where is Option Explicit ? and what does it do ?>>

 Option explicit should be used at the top of every module and it ensures that every variable is actually declared, otherwise you get a compile error.

 Really helps with variable name mis-spells and with variable typing.

Jim.
0
StampITAuthor Commented:
Thanks. Good to know.
0
Dale FyeCommented:
I hope you gave Jim points for all the assistance he provided you.

If not, please go back and do so!
0
StampITAuthor Commented:
Yes. I tried to. Is there a way I can check ?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, one of my comments was tagged as an assist, but no worries anyway as I'm not here for the points.

Jim.
0
Dale FyeCommented:
"I'm not here for the points."

I know, Jim, but it is about, especially in this case where you did 95% of the back and forth and actually taught StampIT how to do the relinking, making sure that others who use this question as a reference see that my LTM tool is only part of the solution, and if they want to add this feature into their applications, the assistance you provided was far more valuable than what I provided.

All I did was provided a link to my LTM tool.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@Dale,

  I was remiss in saying that I appreciated you bringing up the point though.  And as far as I'm concerned, that LTM utility took a heck of a lot more time than what I posted here!

 Have a good day and enjoy the long weekend.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.