Connect to SQL Azure containing backend for MS Access Web App

Question

I created a simple web app using MS Access 2013. It simply had one table with sample data in it. The I launched it to my share point. Before we go ahead, I would like to tell you that I am using Office 365 enterprise version where we need to admin everything like Lync/SharePoint/Exchange etc.

So as per Microsoft blogs and articles and discussions all over, and even the following screenshot suggests that the data stored in the Office 365 is stored in a SQL Azure database for office 2013 web app.

So as the next step, the instructions over web says that if we choose "From Any Location" and enable "Read Write Connection", it should allow you to access the data from any window like SQL Server Management Window.

Ok, my credential looks like this:

Now, when I go to SQL Server Management Studio and use the above credential, and try to login, I get the following error:

I am assuming that there is some setting in our admin page I need to change which can allow me to connect. Any help? I think it seems pretty straight forward to connect it but it is not connecting.

Do we have to fix anything like BCS?

Backwoods

The error message is telling you what to do on the Azure instance. What's the problem? – Dittmer

I cannot connect the db. I don't have any SQL Azure Server. It is something managed automatically by the Microsoft at the back end. So I am in a no clue zone and please don't downvote without confirming. – Backwoods

Anyone please? it is killing me. – Backwoods

You are connecting to an Azure DB Have you logged on to the SQL Azure Portal like the instructions say? How can you have a Azure DB without actually setting it up? There seems to be a major disconnect here. – Dittmer

Hello Preet, As my original post suggests. When you create an Access Web App in Office 2013, you need a sharepoint server to host it. After hosting your web app in Sharepoint 2013, using Office 365, you can enter information in your tables. Now the Microsoft theory suggests that at the backend, these Sharepoint lists are stored in the SQL Azure server which you can Access using the credential given. This is really not any SQL Azure thing but Office 2013. For example, I create a table called Person, now I should be able to access this table using SQL Azure Credentials. Am I clear? – Backwoods

Office 365 db in the cloud? Now the Microsoft theory suggests that at the backend, these Sharepoint lists are stored in the SQL Azure server - So wouldn't that be an azure DB? I'm trying to understand. – Dittmer

If you would like to know more about it : blogs.office.com/b/microsoft-access/archive/2012/08/08/… – Backwoods

certainly it is. What I am trying to say is that using Office 365, you dont get Azure. All you get is SharePoint server, and the data of SharePoint List is by default uploaded to SQL Azure server at the backend, by Microsoft. We dont have any control. Microsoft has offered a new feature where we can access the uploaded data from SQL using the credentials. These credentials will be different for different Access Web Apps. Am I too confusing? – Backwoods

Reading this: When you use Access 2013 to create a web app on SharePoint, Access Services will create a SQL Server or SQL Azure database that houses all of your Access objects, In the process of creating your app in SharePoint, we provision a SQL Server database that will house all of the objects and data that your application requires., If you create an app in Office 365, the database is created in SQL Azure. - So it does create an SQL Azure DB. er a bad provision or bad setup. – Dittmer

Sorry I don't see where I'm mistaken in telling you to correctly configure the Azure DB** IF MS don't provide a way to configure this then I think you need to contact their support. Have you tried the portal? – Dittmer

I am not saying you are wrong. What I am saying is that they didnt give me a SQL Azure Subscription to complete the process of running the firewall thing. They never gave me control over Azure database. – Backwoods

Hello Preet! I got it now, working with such DB, you need to provide the Database Name and "Deafult" just doesn't work. I am so dumb not to realize it. Thanks for the help though. – Backwoods

Great news. You should document that in an answer for future users. – Dittmer

Done. I added the answer at the bottom. – Backwoods

You removed some of your user information in your first screenshot but not in the second one. The Internet is only missing 6 characters in your password. You may want to change your db/username/password. – Jape

Answer 1

On connect server dialog at Sql Server Management Studio select options and insert the name of database that you want to connect

Maturate

If you read my post closely, you will find that the error message I am getting, it does appear only after entering incorrect information in the connect window. – Backwoods

Kill me for my ignorance. Kill me for my ignorance. I feel like the dumbest person on this earth. Damn it. – Backwoods

Answer 2

I am adding this answer in detail because it was really something very easy, yet very difficult to find. So it may help others as well.

While working with Access Web Apps, the data is stored in SQL Azure server, since there is no SQL Azure subscription along with Office 365 Subscription.

The outcome is, that by default, you have set the Connect to Database to "". In such a case, you will need to provide the database name explicitly because you don't have the access to point your request to default database.

Please see the following screenshot,

You will need to enter the database name here, if you don't then the firewall rule will come into picture and your request will be rejected.

Simple, yet strong.

Thanks guys to help me resolve this.

EDIT: If you are still unable to connect, please make sure that you have the latest ODBC driver for connecting to SQL Server 2012. You can download from the Microsoft here:

Microsoft Link for downloading ODBC Driver

Thanks, Vikas

Backwoods

Which version of SQL Server Management Studio did you use? – Speciosity

Hey, I was able to do it in SQL Server 2008. – Backwoods

Answer 3

You are connecting to an Azure DB

There seems to be a major disconnect here. Your DB is on the server under window.net. *Have you logged on to the SQL Azure Portal like the instructions says? *

How can you set up an Azure DB?

Watch this video: http://www.windowsazure.com/en-us/manage/services/sql-databases/

How to Connect using SSMS

This page: http://www.windowsazure.com/en-us/manage/services/sql-databases/how-to-manage-a-sqldb/ tells you exactly what you need to do (which is exactly what your error message is telling you)

---

Step 2: Connect to SQL Database

Connecting to SQL Database requires that you know the server name on Windows Azure. You might need to sign in to the portal to get this information.

Sign in to the Windows Azure Management Portal.

In the left pane, click on SQL Databases.

On the SQL Databases home page, click SERVERS at the top of the page to list all of the servers associated with your subscription. Find the name of the server to which you want to connect and copy it to the clipboard.

Next, configure your SQL Database firewall to allow connections from your local machine. You do this by adding your local machines IP address to the firewall exception list.

On SQL Databases home page, click SERVERS and then click the server to which you want to connect.

Click Configure at the top of the page.

Copy the IP address in CURRENT CLIENT IP ADDRESS.

In the Configure page, Allowed IP Addresses includes three boxes where you can specify a rule name and a range of IP addresses as starting and ending values. For a rule name, you might enter the name of your computer. For the start and end range, paste in the IP address of your computer into both boxes, and then click the checkbox that appears.

The rule name must be unique. If this is your development computer, you can enter the IP address in both the IP range start box and the IP range end box. Otherwise, you might need to enter a broader range of IP addresses to accommodate connections from additional computers in your organization.

Click SAVE at the bottom of the page.

Note: There can be up as much as a five-minute delay for changes to the firewall settings to take effect.

You are now ready to connect to SQL Database using Management Studio.

On the taskbar, click Start, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Management Studio.

In Connect to Server, specify the fully-qualified server name as serverName.database.windows.net. On Windows Azure, the server name is an autogenerated string composed of alphanumeric characters.

Select SQL Server Authentication.

In the Login box, enter the SQL Server administrator login that you specified in the portal when creating your server in the format login@yourServerName.

In the Password box, enter the password that you specified in the portal when creating your server.

Click Connect to establish the connection.

On Windows Azure, each SQL Database logical server is an abstraction that defines a grouping of databases. The physical location of each database might be on any computer in the data center.

In previous versions, you had to connect directly to master when setting up the connection in Management Studio. This step is no longer necessary. Connections will now succeed based on the server name, authentication type, and administrator credentials.

Many of the SSMS wizards you can use for tasks like creating and modifying logins and databases on a SQL Server database are not available for SQL databases on Windows Azure, so you'll need to utilize Transact-SQL statements to accomplish these tasks. The steps below provide examples of these statements. For more information about using Transact-SQL with SQL Database, including details about which commands are supported, see Transact-SQL Reference (SQL Database).

Dittmer

Answer 4

(edit: intended as reply to Costas Kounadis post below, must include DB name on connection)

I completely agree, you MUST enter the database name on the Options tab. This applies to SSMS, SSDT and ODBC connections.

I THINK the reason is that if you do not provide the database name, a request to enumerate the databases available is sent to Azure, which triggers the firewall error message. It might actually be an accurate message because that kind of request may be legit for an Azure database you created yourself, i.e. using standard Azure rather than the auto-provisioned one created by Access.

Towery

The post of Costas Kounadis may be removed, so please place your relevant comment to the appropriate thread – Standice

Answer 5

Just to be clear and provide some searchable text for this problem, Vikas has nailed this: "Cannot open server ... requested by the login. Client with IP address ... is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect" ... (Microsoft SQL Server, Error: 40615)

For those that think an Azure login is required.. there is none, when you use MS Acces to create a web app it imports data from the sources you specify but it then creates a new database on the azure platform and exports the data to that database. So the database cannot be managed through the azure portal (even when if the original data source was an Azure database!). A note of caution: The database structure created is pretty complex with lots of tables devoted to integrating with sharepoint/web app requirements etc. Messing around with the tables too much is inevitably going to break something!

Also worthy of note, using SQL 11 driver you can create ODBC source but not with SQL Native driver as you cannot specify database name until you have logged in to server. The later versions allow you to move to specifying database before connecting (although you may have to enter the password AFTER changing the database name from "default". Or make your own from script:

[ODBC]

DRIVER=SQL Server Native Client 11.0

UID= [userid from MSAccess]

Pwd=[password from MSAccess]

Encrypt=yes

DATABASE= [database from MSAccess]

SERVER=tcp:[server from MSAccess]

Rosenwald

Answer 6

In case you need a ODBC connection, run the ODBC administrator manually, create e.g. a new user data source with SQL Server 11 - there you can specify the default database and then connect to the protected Azure instance for your Access Web App.

Ns

Answer 7

For those still having the problem, see my blog post: https://smindreau.wordpress.com/2014/04/29/access-web-app-2013-client-with-ip-address-is-not-allowed-access-to-the-server/

Key to the solution is ticking the location from which you want to connect in the Access File pane.

Paleobotany