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.
Any suggestions on how to diagnose this would be appreciated.
Thanks!
I
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;
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
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.ne t,1433;Ini tial Catalog=mytestsitedb2;User ID=mytestsitedb2admin@mydb 1;Password ="AComplex Password"
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?
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.
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.
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.
ASKER
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
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?
ASKER
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.
ASKER
Actually, I thought I could ping the site, however, I can't. My site URL is:
mydemosite.azurewebsites.n et
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_r ule @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.
mydemosite.azurewebsites.n
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_r
And I did find a list of Azure data center IP address ranges, however, there are many, and that seems to be very unspecific.
I just found something very interesting for you: http://blog.davidebbo.com/2012/09/managing-database-connections-in-azure.html
ASKER
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.
ASKER
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.
"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.
ASKER
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...
Now the issue is that I can't find an IP address for my site. Getting closer, though...
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, my connection string is a bit different:
Open in new window