Question MS Access 2013 Reporting database connection to SQL Azure Database failing to connect (1 Viewer)

BabaTana

New member
Local time
Today, 14:53
Joined
Sep 26, 2013
Messages
7
I have an ms access 2013 web database on office 365.

I created a reporting ms access database and build reports for distribution to client PC.

the database works fine for computers that are not inside the my client's local area network but gives connection failed when connecting using the computers inside the LAN. Please note the computers have connection to the internet and the users are already using then web app on office 365. I have also installed the SQL Native Client driver 11.0 as i have done on the my dev machine which works fine.

I have also enabled connecting to the database from any location on the internet.

Is there perhaps a firewall setting somewhere that is stopping these computer to connect to the SQL Azure database that I need to change. Please help.....
 

DavidAtWork

Registered User.
Local time
Today, 12:53
Joined
Oct 25, 2011
Messages
699
how are you connecting to the database with PC's on the LAN, post the connection string

David
 

BabaTana

New member
Local time
Today, 14:53
Joined
Sep 26, 2013
Messages
7
Hi David I am using link tables generated by microsoft access automatically when you create an acces database from report on my data option in the back stage of ms access 2013. I think they are called DNS Less ODBC connections?
when pointing to the link table it shows as attached image.
 

Attachments

  • 2013-09-26_121406.jpg
    2013-09-26_121406.jpg
    92.6 KB · Views: 313

DavidAtWork

Registered User.
Local time
Today, 12:53
Joined
Oct 25, 2011
Messages
699
Do you have the SQL Server Native Client installed on each PC, if not you could use the basic SQL Server driver in the connection string.
If the PC's do have it installed then you need to check if the connection string specifies a user id and password, may be worthwhile creating a generic SQL server user/password which can be used in the connection string

David
 

BabaTana

New member
Local time
Today, 14:53
Joined
Sep 26, 2013
Messages
7
Thanks David.
Yes I do have SQL Native client installed on all the computers...
The connection string is exactly the same as it appears on my computer as I am simply copying the same ms access file and redistribute... Wondering if because they are inside a network and possibly they are trying to resolve the server name to a computer on the network and it obviously fails as the database is in the cloud....
 

DavidAtWork

Registered User.
Local time
Today, 12:53
Joined
Oct 25, 2011
Messages
699
try setting up a generic login/password in SQL server, assign public and sysadmin roles and map this user to the SQL Azure database. Then edit the connection string to include the uid and password

David
 

BabaTana

New member
Local time
Today, 14:53
Joined
Sep 26, 2013
Messages
7
Hi David
I am still plagued by the problem.
To do some elimination of possible causes, i took one off the PC off the Local Area Network(LAN) and connected to the Internet through a mobile 3G connection. I set up an ODBC connection to the SQL Azure database it worked fine.
I put back the computer onto the LOCAL area network and tested the ODBC connection again and I am getting the error message below.
I am thinking the problem could be to do with firewall setting on the network, or once the computer is on the LAN it tries to resolve the Windows Azure database server name locally instead of through the internet. or the could be block to outbound connection remote SQL server on the network or my client's ISP.

I have in the meanwhile asked my client infrastructure support provider to look at these possibilities, if you have any further ideas they willl be very much appreciated.

error I am getting is below;

Microsoft SQL Server Native Client Version 11.00.2100

Running connectivity tests...

Attempting connection
[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53].
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

TESTS FAILED!
 

DavidAtWork

Registered User.
Local time
Today, 12:53
Joined
Oct 25, 2011
Messages
699
Baba, you could try creating a test blank database to check LAN connection.
Paste this code into a module. This is basic code to connect to a SQL server database without the need to set up a local ODBC connection.
Edit the variables to suit and run it and it should just link the one table

Code:
Dim strConnectionString, strServer, strDatabase, strUID, strPWD As String
Dim strTableName As String
'assign logon details to variables used in the connection string
strServer = "myServer"
strDatabase = "myDB"
strUID = "muUser"
strPWD = "myPassword"
strTableName = "myTable"
 
'this uses just the standard SQL server driver, but you can substitute
'the value "Driver={SQL Server}" to the latest version if you have Native client installed on the machine
 
strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=" & strServer & ";" & _
"Database=" & strDatabase & ";" & _
"Uid=" & strUID & ";" & _
"Pwd=" & strPWD
 
DoCmd.TransferDatabase acLink, "ODBC Database", _
            strConnectionString, acTable, strTableName, strTableName

David
 

AccessJunkie

Senior Managing Editor
Local time
Today, 05:53
Joined
May 11, 2006
Messages
278
Hi,

I discussed your issue with some people on our team and we are thinking it might be a DNS resolution problem.

Here is something that was suggested:

>>>>>
1. Get the IP of the server, using a machine that can connect to it. Let’s say the server is y6fgzqj45e.database.windows.net and the IP is 124.53.34.2.
2. Edit the hosts file on one machine in the LAN %windir% \System32\Drivers\etc\hosts to add an entry like this:
124.53.34.2 y6fgzqj45e.database.windows.net
3. If you can now connect to SQL Azure using that machine, then it means you have to solve this issue at a higher level in the LAN.
>>>>>

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
 

BabaTana

New member
Local time
Today, 14:53
Joined
Sep 26, 2013
Messages
7
Hi All
Thank you for your help the problem was caused by ports blocked by the ISP.
I got the pointers from the thread with a summary below <cant attach a link unfortunately>


The summary solution from the link above is as below;

Windows Azure SQL Database (formerly known as SQL Azure) works exclusively and only on TCP port 1433. It only support SQL Server Authentication, TCP connection and TDS protocol as of today.

In order to successfully establish connection to SQL Azure one must fulfil the following requirements:
•Create SQL Azure server & Database
•Setup SQL Azure Server's firewall rules to accept connections from the IP address of application that will connect to that server
•Make sure the box (be it Virtual, or home, or whatever) has no blocking outbound TCP port 1433
•Explicitly force encryption in connection string
•Explicitly chose to not trust server certificate in connection string

Please note that many (if not all) ISPs (Internet Service Providers) and Hosters, as well as IT staff within companies DO block outgoing TCP Port 1433 due to the SQL Slammer worm. This outgoing port blocking appears to be one of the most faced issues of newcommers to SQL Azure.
 

Users who are viewing this thread

Top Bottom