Connecting Microsoft Access to Azure SQL database for remote users
I'm trying out Azure SQL database for the first time. The migration and default connection from the office went fine. However, the db is a BE to an Access FE. Access needs to connect from remote locations without fixed IP addresses. I would also like Access to use a login that is not the server login.
Again, I'm brand new to Azure. After reading until my eyes nearly popped out, I understand that Azure supports a contained database and I "should" be able to set up users directly in the database security without giving then access to other databases. I get an error trying to connect using this test user account from both, within and outside the office.
From outside .the office I can't even connect my server admin account. I get an IP error even though I created a firewall rule in the database to allow all IP addresses.
Both of those above are within a particular db, not the server.
Does anyone have any ideas?
Again, I'm brand new to Azure. After reading until my eyes nearly popped out, I understand that Azure supports a contained database and I "should" be able to set up users directly in the database security without giving then access to other databases. I get an error trying to connect using this test user account from both, within and outside the office.
From outside .the office I can't even connect my server admin account. I get an IP error even though I created a firewall rule in the database to allow all IP addresses.
Both of those above are within a particular db, not the server.
Does anyone have any ideas?
Understand your pain. I don't have a static IP address either, so occasionally I will be unable to connect to one of my client's Azure databases and have to open the azure portal and update my IP address to a range of IPs.
Anxiously awaiting other responses.
Anxiously awaiting other responses.
ASKER
Hi Icohan,
It's crossed my mind. But the Azure db is meant to replace the in-house SQL server.
Thank you for the suggestion.
It's crossed my mind. But the Azure db is meant to replace the in-house SQL server.
Thank you for the suggestion.
I get an IP error even though I created a firewall rule in the database to allow all IP addresses.I think you should start from here..if a simple IP change destroys connections ...you need to resolve this first.
ASKER
Hi Dale,
What I don't understand is why I can't connect if I've created a firewall rule to allow all IP addresses.
1
Allow All
0.0.0.0 255.255.255.255
2
Allow Azure
0.0.0.0 0.0.0.0
The other issue (or maybe it's all related) is that the new user I created "within" the database (as opposed to a SQL Server Login) can't connect even from the office. I gave that account datareader and datawriter access.
I think my next "try" at this will be to move both of those, the user and the firewall rule, outside the db to the server itself.
What I don't understand is why I can't connect if I've created a firewall rule to allow all IP addresses.
1
Allow All
0.0.0.0 255.255.255.255
2
Allow Azure
0.0.0.0 0.0.0.0
The other issue (or maybe it's all related) is that the new user I created "within" the database (as opposed to a SQL Server Login) can't connect even from the office. I gave that account datareader and datawriter access.
I think my next "try" at this will be to move both of those, the user and the firewall rule, outside the db to the server itself.
ASKER
Hi John,
Yes, that is exactly what I need to do. I just need some ideas of what to try or fix.
Yes, that is exactly what I need to do. I just need some ideas of what to try or fix.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Probably posting some screenshots will be helpful...
Start with RDPing to your work machine (IF i understood correctly Azure works there) from home to check why you get your home IP rejected
Start with RDPing to your work machine (IF i understood correctly Azure works there) from home to check why you get your home IP rejected
ASKER
You were right Anders. I did keep forgetting to do that. I also had to add the user to the dbo role. EXEC sp_addrolemember N'db_owner', N'xxxxx'
Now I can make the connection from SSMS at the office. But I still can't make the connection through Access. I get the same message as always: Connection Failed
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'XXXX'.
The only way I've found around this is to add the user login to the master database.
Now I can make the connection from SSMS at the office. But I still can't make the connection through Access. I get the same message as always: Connection Failed
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'XXXX'.
The only way I've found around this is to add the user login to the master database.
ASKER
Hi John,
Apparently Azure SQL Database has a built-in firewall based on IP addresses. So if the IP address trying to connect has not been whitelisted then the connection fails.
I tried opening the entire range using 0.0.0.0 to 255.255.255.255, but the connection still failed. I also tried 1.1.1.1 to 255.255.255.255 in case the 0's conflicted with Azure services. But, no luck. I'm going to experiment a little more from home tonight.
Apparently Azure SQL Database has a built-in firewall based on IP addresses. So if the IP address trying to connect has not been whitelisted then the connection fails.
I tried opening the entire range using 0.0.0.0 to 255.255.255.255, but the connection still failed. I also tried 1.1.1.1 to 255.255.255.255 in case the 0's conflicted with Azure services. But, no luck. I'm going to experiment a little more from home tonight.
What does your connection string look like in Access?
ASKER
I finally got the connection to work through Access this morning.
I had to create a dsn file using Administrative Tools. Then I had to edit the file and manually add line 4, DATABASE=DatabaseName, resave it and then use it to connect via Access. Even though I was designating the database name in the Access connection wizard, it still wouldn't make the connection.
Now I'm thinking maybe this file is not easily transferable to other machines since it specifies the ComputerName connecting.
[ODBC]
DRIVER=ODBC Driver 13 for SQL Server
UID=UserName
DATABASE=DatabaseName
WSID=ComputerName
APP=Microsoft Office
SERVER=tcp:servername.database.windows.net,1433
Description=Description
I had to create a dsn file using Administrative Tools. Then I had to edit the file and manually add line 4, DATABASE=DatabaseName, resave it and then use it to connect via Access. Even though I was designating the database name in the Access connection wizard, it still wouldn't make the connection.
Now I'm thinking maybe this file is not easily transferable to other machines since it specifies the ComputerName connecting.
[ODBC]
DRIVER=ODBC Driver 13 for SQL Server
UID=UserName
DATABASE=DatabaseName
WSID=ComputerName
APP=Microsoft Office
SERVER=tcp:servername.database.windows.net,1433
Description=Description
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Dale. I'll take a look at it.
ASKER
OK, folks, everything is connected and working as it should. The outside connections just needed time for the DNS servers to populate the change in allowed range.
Dale, I think I've used your LTM before. It's awesome.
I very much appreciate everyone's help.
Dale, I think I've used your LTM before. It's awesome.
I very much appreciate everyone's help.
If you don't have a on-premise database you could easily creat one and have your Access db users connecting to this one instead of directly to Azure managed instance SQL database.