Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 9 votes, 5.00 average. Display Modes
Old 09-26-2013, 12:29 AM   #1
BabaTana
Newly Registered User
 
Join Date: Sep 2013
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
BabaTana is on a distinguished road
MS Access 2013 Reporting database connection to SQL Azure Database failing to connect

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.....

BabaTana is offline   Reply With Quote
Old 09-26-2013, 01:59 AM   #2
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

how are you connecting to the database with PC's on the LAN, post the connection string

David
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
The Following User Says Thank You to DavidAtWork For This Useful Post:
BabaTana (10-08-2013)
Old 09-26-2013, 02:16 AM   #3
BabaTana
Newly Registered User
 
Join Date: Sep 2013
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
BabaTana is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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.
Attached Images
File Type: jpg 2013-09-26_121406.jpg (92.6 KB, 224 views)

BabaTana is offline   Reply With Quote
Old 09-26-2013, 02:23 AM   #4
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
The Following User Says Thank You to DavidAtWork For This Useful Post:
BabaTana (09-27-2013)
Old 09-26-2013, 02:36 AM   #5
BabaTana
Newly Registered User
 
Join Date: Sep 2013
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
BabaTana is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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....
BabaTana is offline   Reply With Quote
Old 09-26-2013, 02:56 AM   #6
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
The Following User Says Thank You to DavidAtWork For This Useful Post:
BabaTana (09-27-2013)
Old 09-27-2013, 04:24 AM   #7
BabaTana
Newly Registered User
 
Join Date: Sep 2013
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
BabaTana is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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!

BabaTana is offline   Reply With Quote
Old 09-29-2013, 11:33 PM   #8
DavidAtWork
Newly Registered User
 
Join Date: Oct 2011
Location: leighton Buzzard, UK
Posts: 699
Thanks: 1
Thanked 166 Times in 164 Posts
DavidAtWork is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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
__________________
Help is only a forum question away
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
DavidAtWork is offline   Reply With Quote
The Following User Says Thank You to DavidAtWork For This Useful Post:
BabaTana (10-08-2013)
Old 10-01-2013, 12:32 PM   #9
AccessJunkie
Senior Managing Editor
 
AccessJunkie's Avatar
 
Join Date: May 2006
Location: Rogue Squadron
Posts: 277
Thanks: 0
Thanked 69 Times in 45 Posts
AccessJunkie will become famous soon enough
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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
----------
AccessJunkie is offline   Reply With Quote
The Following User Says Thank You to AccessJunkie For This Useful Post:
BabaTana (10-08-2013)
Old 10-08-2013, 08:18 AM   #10
BabaTana
Newly Registered User
 
Join Date: Sep 2013
Posts: 7
Thanks: 5
Thanked 0 Times in 0 Posts
BabaTana is on a distinguished road
Re: MS Access 2013 Reporting database connection to SQL Azure Database failing to con

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.

BabaTana is offline   Reply With Quote
Reply

Tags
ms access 2013 , odbc connection , reports , sql azure

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access - Azure SQL ODBC Connection alevin16 Modules & VBA 4 10-09-2014 09:01 AM
Access 2013 Web Database-Trying to display an image on a report with desktop database kmcclaff Reports 0 08-05-2013 03:35 PM
Access 2013 and SQL Azure Web Apps Rx_ Site Suggestions 0 08-08-2012 12:28 PM
Question Database connection in excel to get data from password protected Access database AccessToAcess General 2 08-06-2012 06:40 AM
Code connect to database and query select data from database in Ms Access 2007 veasna Queries 2 11-29-2010 12:25 PM




All times are GMT -8. The time now is 04:49 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World