Link to home
Start Free TrialLog in
Avatar of codequest
codequest

asked on

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

Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

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

Avatar of codequest
codequest

ASKER

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?
>>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.
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
can't you ping your site?
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.
it is all described in my article. Check figure 8 and its surrounding.
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.
This is all I see at Azure DB's > Select DB > Configure tab

User generated image
Also, I can access the Azure SQL DB from my local Management Studio using the proper connections, so I know it's there.
Sure because your IP address is trusted.
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.
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...
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.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial