UtterAccess.com
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
> Odbc Connection To Azure, Access 2016    
 
   
aburl81
post Jun 2 2020, 08:25 AM
Post#1



Posts: 411
Joined: 24-February 06
From: Corona, CA


Hi UA,

Im trying to link my tables in MS Azure to a MS Access DB using Active Directory Authentication. So far I have only been able to successfully link using a Machine Data Source DSN which works fine for me but not other users. I am unable to create a file DSN (not sure if I even need one or if that would help). I have also tried using the connection string below which links the table but produces the error attached and the login screen also attached. It does this every time I open the form and I have to put the same login info twice. Error also pops up twice as well. I understand that each user must login but this seems painful and there has to be a more user friendly solution.

CODE
Driver={ODBC Driver 17 for SQL Server};Server=tcp:wat-ds118-SQL-001-p.database.windows.net,1433;Database=DS118Additives;Encrypt=yes;TrustServerCerti
ficate=no;Connection Timeout=30;Authentication=ActiveDirectoryInteractive


Attached File  error1.jpg ( 61.87K )Number of downloads: 0


Attached File  login.jpg ( 43.49K )Number of downloads: 0

--------------------
Adan
Go to the top of the page
 
cheekybuddha
post Jun 2 2020, 09:00 AM
Post#2


UtterAccess Moderator
Posts: 13,070
Joined: 6-December 03
From: Telegraph Hill


Hi,

Try using connection string:
CODE
  Dim strCn As String

  strCn = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=wat-ds118-SQL-001-p.database.windows.net;PORT=1433;DATABASE=DS118Additives;Encrypt=yes;Trusted_Con
nection=yes;"


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
aburl81
post Jun 2 2020, 09:29 AM
Post#3



Posts: 411
Joined: 24-February 06
From: Corona, CA


Thanks Cheeky, think that worked. I dont receive a login request anymore. I am also using the following code to relink tables on startup.

CODE
Sub tConnectStrUpdate()
Dim t As TableDef
Dim db As Database
Dim sSQL As String

sSQL = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=wat-ds118-SQL-001-p.database.windows.net;PORT=1433;DATABASE=DS118Additives;Encrypt=yes;Trusted_Con
nection=yes;"

'sSQL = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=tcp:wat-ds118-SQL-001-p.database.windows.net,1433;APP=Microsoft® Windows® Operating System;DATABASE=DS118Additives;Authentication=ActiveDirectoryInteractive;"

Set db = CurrentDb
Debug.Print db.Connect
db.Connect = sSQL

' filter our the hidden SYS tables because you don't want to update those connection strings
For Each t In db.TableDefs
    Debug.Print t.Name, t.Connect
    
    If Left(t.Name, 4) = "dbo_" Then
        t.Connect = sSQL
        t.RefreshLink
    End If
    
Next t
End Sub

--------------------
Adan
Go to the top of the page
 
aburl81
post Jun 2 2020, 12:25 PM
Post#4



Posts: 411
Joined: 24-February 06
From: Corona, CA


Update,

Connection string seems to work for me ok, I had another user test on his computer and he is getting the same double login that I was getting at first. Also I noticed that I am unable to add new records now for some reason.

--------------------
Adan
Go to the top of the page
 
cheekybuddha
post Jun 2 2020, 01:50 PM
Post#5


UtterAccess Moderator
Posts: 13,070
Joined: 6-December 03
From: Telegraph Hill


Hi Adan,

Restart your copy of access (completely shut Access down, don't just close the db) and then re-open the db again on your machine.

Check it still works.

--------------------


Regards,

David Marten
Go to the top of the page
 
aburl81
post Jun 3 2020, 01:09 PM
Post#6



Posts: 411
Joined: 24-February 06
From: Corona, CA


Ok, restarted my computer and checked it again this morning and it will connect for me without being prompted for login but I'm still unable to add new records from form or from table. I have not check connection yet on the other user. Also I have to connect thru a full tunnel VPN since I am working from home and my IP is not static.

--------------------
Adan
Go to the top of the page
 
AlbertKallal
post Jun 3 2020, 01:22 PM
Post#7


UtterAccess VIP
Posts: 3,105
Joined: 12-April 07
From: Edmonton, Alberta Canada


This usually occurs if the linked table does not see a PK. Or perhaps it does not have a autonumber PK, and you not providing the PK>

Flip the linked table into design mode in Access. Does it show the PK, and also take a quick look at the field types - especially for dates - you want to ensure they are seen as date/time columns and not text.

As for a FILE dsn? Well, if you linking using Access and want DSN-less connections, then you REALLY want to find out why you can't link using a ILE dsn. The reason for this is that Access automatically converts a FILE dsn to dsn-less for you. That means if you deploy to other workstations, you don't have to setup or configure a DSN on each machine.

I often find with win10, that the FILE dsn fails due to lack of permissions as to where you are saving the FILE dsn. One way to get around this is to launch Access (right click on a short cut) and choose run as administrator.

So, you kind of want to fix/find out why a FILE dsn does not work - but using them means Access will automatic convert that to DSN-less for you.

So, I would check the linked table (design mode) and ensure that access sees a PK for that linked table. You can then just close table design mode, and click on the linked table - can you edit rows? (yes/no). And can you add rows? (yes/no).

R
Albert


Go to the top of the page
 
aburl81
post Jun 3 2020, 02:00 PM
Post#8



Posts: 411
Joined: 24-February 06
From: Corona, CA


Hi Albert,

I checked the linked table in design view and it doesn't show a PK. The tables were setup in SQL Management Studio. I setup the PK as an int and made sure that Identity Specification was set to yes. I linked the table using machine DSN and access asked me to choose a unique record identifier. I assumed this would set the PK. When I open the table in design mode there is no key symbol next to my PK field. How can that be fixed? As for the File DSN, I am not sure. I am working from a company computer so it is possible that permissions prevent me from creating a file dsn. I used the same steps as I did to create the machine dsn but I only receive errors saying connection failed. Tried creating thru access and thru the ODBC app on the computer. Unable to run as administrator, I get an error saying that I don't have access and to contact IT.

--------------------
Adan
Go to the top of the page
 
aburl81
post Jun 3 2020, 02:48 PM
Post#9



Posts: 411
Joined: 24-February 06
From: Corona, CA


Think I got it, I added the following to my table re-linking code

CODE
CurrentDb.Execute "CREATE UNIQUE INDEX __uniqueindex ON dbo_tbl_test (test_id) WITH PRIMARY"

--------------------
Adan
Go to the top of the page
 
aburl81
post Jun 3 2020, 04:19 PM
Post#10



Posts: 411
Joined: 24-February 06
From: Corona, CA


Just checked with one of the users and they are able to connect but only after entering login twice and each time they open a new form. Is there a way to create a DSN-less connection without a file DSN? user is unable to create a machine DSN as well.

--------------------
Adan
Go to the top of the page
 
ChromaticZero
post Jun 3 2020, 10:20 PM
Post#11



Posts: 29
Joined: 7-September 15



I think the reason you're seeing the credential form is because you're not including your Azure database credentials in the ODBC connection string.... perhaps? Seems to me the password needs to be saved on the table link.

CODE
"ODBC;DRIVER=ODBC Driver 17 for SQL SERVER;SERVER=wat-ds118-SQL-001-p.database.windows.net;PORT=1433;DATABASE=DS118Additives;UID=<USERNAME>;PWD=<PASSWORD>;TRUSTED_CONNECTION=No;"


If it's a trusted connection, wouldn't it use your windows credentials, which likely aren't configured within Azure? Or is that assuming too much? Either that or perhaps your Azure configuration doesn't have the other users Active Directory credentials authenticating properly?
Go to the top of the page
 
aburl81
post Jun 5 2020, 08:51 AM
Post#12



Posts: 411
Joined: 24-February 06
From: Corona, CA


Chromatic, thanks for the reply. that produces the same double login requirement. I am attempting to create the DSN through code now which seems to work for me. I still need to test it with other users. I am using the register database method found here.

--------------------
Adan
Go to the top of the page
 
cheekybuddha
post Jun 5 2020, 08:54 AM
Post#13


UtterAccess Moderator
Posts: 13,070
Joined: 6-December 03
From: Telegraph Hill


Just to check, the other user[s] have their own login to the SQL server (via Active Directory) ?

--------------------


Regards,

David Marten
Go to the top of the page
 
aburl81
post Jun 7 2020, 01:03 AM
Post#14



Posts: 411
Joined: 24-February 06
From: Corona, CA


Yes, currently have 3 AD's as follows:

1. Owners - Full Control
2. Contributors - Read/Write
3. Read Only

--------------------
Adan
Go to the top of the page
 
AlbertKallal
post Jun 9 2020, 01:19 AM
Post#15


UtterAccess VIP
Posts: 3,105
Joined: 12-April 07
From: Edmonton, Alberta Canada


Minor issue:
You in general should not need to execute that access command to see/set the PK. You only as a general rule need to do this if you are linking to a SQL server view.

But, for a linked table, the PK should show - and show just about no matter what process you used to link the table (code, or the Access UI).

While you have setup the "ID" as identity -that ONLY gives you a auto incrementing column.

However, setting a column as identity does not give you nor make this a PK column. (it does however give you the autoincrement column).

While in SQL server tools, there is a PK button you can hit when the "ID" column has the focus in the menu bar
(in fact it just like the one in Access table designer and it does the same thing).

So, if these are tables and not views? Then you need/want to ensure that the column is identity (the auto increment), and also hit the PK button.

So, for table linking, you should see the PK be set in Access, and there should be no need to set the PK from Access. This should ONLY be required if you are linking to a view, since views really don't have a PK - so you have to tell Access which column is going to be the PK.

If you not seeing a PK automatic come down after linking a table, then you want to address this issue. If you don't, then Access will have all kinds of trouble to update such tables. You can fool or force the issue by executing the "index" command. It does not actually create a index in access, but it is the "means" by which you can tell Access which column to use as a PK when in fact there is not one!

So, I would address this issue. This is especially the case if you going to setup any relationships on the server. You need those PK's set, and both the SQL table designer, and the access client side table designer will show the familiar access "gold key" that both SQL server and Access use to show the PK in table design mode.

So setting the identify specification gets you the auto increment feature, but it does not set the PK for you.

In fact, you can do the same thing with a local Access table. You can set a column to auto number, but not necessary have a PK defined for that table.

However, in the case of SQL server and linked tables? They are in general "read only" if you link to a table without a PK.

But, you can of course fudge this with your index command to tell access what to use for the PK - but you may well now have some SQL server tables without a PK - and you don't want that setup at all before you get too deep into the project.

R
Albert


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 08:40 AM