Azure DB access issue

I'm doing some tests to learn Azure SQL functionality.
I made a very simple asp.net application in VS2013.  
Started with a generated website, that has the ASP.NET welcome page.

Added an MDF:
DB table "Names" has three columns.
ID = identity, bigint
Name = nchar(10)
Description = nvarchar(50)

I built a "Names.aspx" page that has a gridview and detail view into the Names table, linked in a master/detail relationship.

To simplify things I change the name of that form to "Default.aspx" when I want to run it instead of the generated Default.aspx.

This all works fine on my PC in debug mode.  I can add table rows, edit, delete, etc.

And it publishes fine to my little test azurewebsites.net site.   If I use the generated Default page I can see it fine.

However, if I change Default to the "Names" page, I get the error below.

The Azure site-generated connection string in my Azure website Configuration tab looks this, with the correct DB name, user and password.

Data Source=tcp:mydb.database.windows.net,1433;Initial Catalog=MySite_db;User Id=me@MySite;Password=ComplexPassword;

Open in new window


Any suggestions on how to diagnose this would be appreciated.

Thanks!

I
Invalid object name 'dbo.Names'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.Names'.

[SqlException (0x80131904): Invalid object name 'dbo.Names'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1787814
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5341674
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +546
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1693
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +377
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1421
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +137
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +160
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +108
   DataSetTableAdapters.NamesTableAdapter.GetData() +80

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
   System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +192
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +155
   System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +486
   System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1609
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +138
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +30
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
   System.Web.UI.Control.EnsureChildControls() +83
   System.Web.UI.Control.PreRenderRecursiveInternal() +42
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974

Open in new window

LVL 2
codequestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
Have you allowed the IP address of your web site? Have a look at http://emoreau.com/Entries/Articles/2014/07/Data-in-Azure--Part-1.aspx.

Also, my connection string is a bit different:
Return "Server=tcp:ltgftgkor2.database.windows.net,1433;Database=Test;User ID=loginname@ltgftgkor2;Password=password1!;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

Open in new window

0
codequestAuthor Commented:
Thanks for the input.   So, big learning curve here; I mistakenly thought that Azure would link to the files in my App_Data folder.  Clearly not so.

So I
>  used management studio to deploy the database to Azure,
>  made sure my connection with Azure SQL Server allowed access to Azure Services.
>  linked the DB to my web-site as a Linked Resource
>  checked that the connection string was in the website Configure tab, and had the correct password and db name.

The connection string looks like this:

Data Source=tcp:mydb1.database.windows.net,1433;Initial Catalog=mytestsitedb2;User ID=mytestsitedb2admin@mydb1;Password="AComplexPassword"

So at least now there's a chance that it will work, however, I still get the same error.

Do I need to update the configuration string in my web.config file locally?
Any other suggestions?
0
Éric MoreauSenior .Net ConsultantCommented:
>>I mistakenly thought that Azure would link to the files in my App_Data folder

This doesn't work with Azure.

>>however, I still get the same error.

What about adding the IP address of your host to the list of allowed IP addresses. This is also a requirement.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

codequestAuthor Commented:
Thanks for the response.  Where would I find the controls to add IP address of host to list of allowed IP addresses?
Update:  I'm looking here:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings/#database-level-firewall-rules
0
Éric MoreauSenior .Net ConsultantCommented:
can't you ping your site?
0
codequestAuthor Commented:
I can ping the site, however, I'm not sure what to do with the IP address once I  have it.  I'm looking at that link above, maybe that will tell me.
0
Éric MoreauSenior .Net ConsultantCommented:
it is all described in my article. Check figure 8 and its surrounding.
0
codequestAuthor Commented:
Actually, I thought I could ping the site, however, I can't.  My site URL is:
mydemosite.azurewebsites.net
When I ping that, ping says "could not find host"

On the other hand, I did figure out where to enter the firewall rule in the DB, as as query of the form:

EXEC sp_set_database_firewall_rule @name = N'mydemosite_FW_Rule_01', @start_ip_address = '192.168.1.11', @end_ip_address = '192.168.1.11'

And I did find a list of Azure data center IP address ranges, however, there are many, and that seems to be very unspecific.
0
codequestAuthor Commented:
This is all I see at Azure DB's > Select DB > Configure tab

Snap of Azure DB Config page
0
Éric MoreauSenior .Net ConsultantCommented:
0
codequestAuthor Commented:
Also, I can access the Azure SQL DB from my local Management Studio using the proper connections, so I know it's there.
0
Éric MoreauSenior .Net ConsultantCommented:
Sure because your IP address is trusted.
0
codequestAuthor Commented:
The main stuck point now seems to be that I can't find the control for
"Add to Allowed IP Addresses"
I can see it in your article, and I can see it in this article http://www.codemag.com/Article/1305041
However, I only see the image above in my Azure Dashboard for Databases.
0
codequestAuthor Commented:
Ah, now I found it, it's under the server, not the individual DBs.  You have to click on the server cell in one of the DB rows to get to the server.

Now the issue is that I can't find an IP address for my site.   Getting closer, though...
0
codequestAuthor Commented:
Looks like some kind of double bind here.  The "Free" level of websites does not seem to have any fixed IP address.  But an IP address is required to allow DB to be accessed by the site.  I'm SURE this must work somehow;  it would be a giant hole if it did not.  However, it is not clear at all how to get the IP address for the website to put into the firewall rule.
0
Éric MoreauSenior .Net ConsultantCommented:
Did you look at my second link? David seems to be explaining exactly that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Azure

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.