none
SQL Azure Step in Web App Tutorial - works locally, SQL Azure Server and databases can be connected to, published to Azure, error

    Question

  • SQL Azure step in Web App Azure tutorial "an error occurred while processing your request"

    Running thro tutorial
    .NET Web Applicatioon with SQL Azure.

    Ran successfully to Step 8 "Creating Scaffolding to Create/Read/Update/Delete To Do Items"

    Set-Up SQL Azure
    Step 1 - 9 performed successfully

    Step 10 - ensure local firewall allows outbound TCP on port 1433, created firewalls rules for SQL Azure and ticked "Allow other Microsoft Services to access this server"

    I can go to the SQL Azure Management Portal and connect to the server using the admin user and password.

    Set Up Your Application to Use the Database

    Step 2 I replaced the section in Web.Release.Config with the connectionStrings section mentioned. I Used the connection string by viewing the connection strings property for the master database in the Windows Azure management portal.

    Run your application in the cloud
    I changed the ServiceDefinition.csdef file so that the Sites section was uncommented ( I think there is an error in the tutorial description) which shows that it is still commented out.

    Step 2 I published the application using the Release Configuration.

    Deployment completed.

    When I click on the URL I get "Sorry an error occurred while processing your request".

    Troubleshooting the SQL Azure Connection

    I can run SQLCMD -U -P -S from my command line and I am connected to the database

    From the Azure Management Portal I can see the server and the databases. I can test connectivity to all successfully. I can log into the SQL Azure Portal.


    Pinging the SQL Azure server resolves to an ip address.

    I can telnet to port 1433 on the server OK.

    Tracertr returns no information at the 14th hop and thereafter.


    Here is my web.release.config. Have you any clues as to why I am getting the error?

    <?xml version="1.0"?>

    <!-- For more information on using web.config transformation visit http://go.microsoft.com/fwlink/?LinkId=125889 -->

    <configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
      <!--
        In the example below, the "SetAttributes" transform will change the value of
        "connectionString" to use "ReleaseSQLServer" only when the "Match" locator
        finds an atrribute "name" that has a value of "MyDB".
       
        <connectionStrings>
          <add name="MyDB"
            connectionString="Data Source=ReleaseSQLServer;Initial Catalog=MyReleaseDB;Integrated Security=True"
            xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
        </connectionStrings>
      -->
      <connectionStrings>
        <add name="ApplicationServices"
             connectionString="Server=tcp:te3j65qnt3.database.windows.net,1433;Database=master;User ID=xxxxxx@te3j65qnt3;Password=xxxxxxx;Trusted_Connection=False;Encrypt=True"
           xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
      </connectionStrings>
      <system.web>
        <compilation xdt:Transform="RemoveAttributes(debug)" />
        <!--
          In the example below, the "Replace" transform will replace the entire
          <customErrors> section of your web.config file.
          Note that because there is only one customErrors section under the
          <system.web> node, there is no need to use the "xdt:Locator" attribute.
         
          <customErrors defaultRedirect="GenericError.htm"
            mode="RemoteOnly" xdt:Transform="Replace">
            <error statusCode="500" redirect="InternalError.htm"/>
          </customErrors>
        -->
      </system.web>
    </configuration>

    Friday, December 16, 2011 5:35 PM

All replies

  • Hi,

    Your connection string has "Database=master", shouldn't be you database name instead ? 

    Friday, December 16, 2011 6:19 PM
  • Hi,

    Victor is correct. You need to specify a correct database name. In addition, check your SQL Azure firewall settings to see if you allow connection from Microsoft services. Since the issue is related to SQL Azure, if you have further questions, you can post a thread on the SQL Azure forums, there are more related experts in that forums.

     

    Best Regards,

    Ming Xu.


    Please mark the replies as answers if they help or unmark if not.
    If you have any feedback about my replies, please contact msdnmg@microsoft.com.
    Microsoft One Code Framework
    Tuesday, December 20, 2011 6:56 PM
    Moderator
  • I don't know if there was an official resolution was to the issue of this post of getting the tutorial to work successfully.  (http://www.windowsazure.com/en-us/develop/net/tutorials/web-app-with-sql-azure/)

    I'm having the same one -- and I believe my connection string is correct, referencing "ToDoDb" as the database.

    I can deploy the ToDoList app from a local instance of IIS running  ASP.NET and the application works.  I modified my connection string to connect to the actual SQLAzure database on the back end using my actual SQLAzure (production) account instead of a local version.

    The App "talks" to SQLAzure on my dev / local machine, but when I deploy the app fully and try to hit it in production I get the same error. I see the ASP page rendered, but for some reason I can't connect to the database.   The message displayed on the page is:  "Sorry, an error occurred while processing your request"

     Also, I tried bringing down the firewall constraints, entering a range of 0.0.0.0 to 255.255.255.255, so I don't think it's a firewall configuration issue.  (I do let my local dev box through the firewall).

    Thanks for any help or guidance! 


    • Edited by DevBox Tuesday, March 13, 2012 4:49 PM
    Tuesday, March 13, 2012 3:38 PM
  • Hello DevBox,

    I had the same problem and after trying for almost two days I was successful with these connectionstrings - set in the connectionstrings section of the original web.config
     - not in the web.release.config.


    <add name="ApplicationServices" connectionString="data source=ServerName.database.windows.net;Initial Catalog=ToDoDb;
     User ID=UserName@ServerName;Password=PassWord;Encrypt=true;Trusted_Connection=false;
     MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
     <add name="ToDoDb" connectionString="data source=ServerName.database.windows.net;Initial Catalog=ToDoDb;
     User ID=UserName@ServerName;Password=PassWord;Encrypt=true;Trusted_Connection=false;
     MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
     <add name="DefaultConnection" connectionString="data source=ServerName.database.windows.net;Initial Catalog=ToDoDb;
     User ID=UserName@ServerName;Password=PassWord;Encrypt=true;Trusted_Connection=false;
     MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>

    As you will have expected you should exchange the words ServerName, UserName, and PassWord with your credentials.
    (When you put in the servername into the combination UserName@ServerName just use the first part of the servername - not the whole string 'ServerName.database.windows.net'.
    This is only needed in the data source= part.

     

    You should also insert into the System.Web section of your original Web.config the entry

    <customErrors mode="Off"></customErrors>

    That will show you a more detailed error message if still an error occurs.

    Hope that helps.

    Good luck

    asWorks


    ps.

    The application will only create the ToDoDb database if it not exists. If you, like I did, created the database manually without creating the table you will get an
    error 'item not found'.

    Wednesday, March 14, 2012 2:09 PM
  • Hi asWorks,

    Thank you for the info.  I modified by web.config connection string to have three as you suggested:  "ApplicationServices", "ToDoDb", and "DefaultConnection", all pointing to my SQL Azure production database.

    I still have the same issue -- it works on my local machine (connecting directly to SQL Azure), but not on SQL Azure. 

    *** I have one question for you that may help me identify the problem -- Which environment are you running Visual Studio 2010 to upload to Windows Azure:  32 bit or 64 bit? ***

    I have the following debug message "flash" at me when I compile (I have take a screenshot to read it before it disappears):

    Windows Azure Hosted Services run in a 64-bit environment.  The use of native code or .Net Full Trust features such as
    P/Invoke may require migration to 64-bit.  See http://go.microsoft.com/fwlink/?LinkId=145047 for details.
    (link goes to:  http://msdn.microsoft.com/library/dd573362.aspx)  

    The key phrase is "may require migration."  I may need to reinstall Visual Studio to run 64-bit. (I have32 bit set up for previously running older tutorials that were set up with that config).  I'm not sure whether or not I'm using P/Invoke in the tutorial as noted in the debug message.

    Thanks again!

    DevBox

     

    Friday, March 16, 2012 7:50 PM
  • Hi DevBox,

    sorry to hear that it still doesn´t work for you.

    I am running a 64 Bit system. Unfortunately I have no idea about that P/Invoke problem.

    Hope someone else can help you out with this.

    Good luck

    asWorks

    Saturday, March 17, 2012 7:28 PM
  • Hi asWorks,


    Thanks again for your assistance and for the information on yours system..  I tried to complete the tutorial on a Windows 64 bit installation of Visual Studio 2010 on a co-worker's computer.  Had the same problem -- we can set up the initial web pages on WindowAzure, but when deploying the production application that points to the database we still get the message: "Sorry, an error occurred while processing your request".  


    The page is rendering properly, but something appears to be preventing the database call from executing.  This seems to point to a firewall / port 1433 issue,  it connects fine to the database if the web server is on my local machine.  


    For anyone:  What would the firewall settings need to be to allow an Azure production web server to connect to the SQL Azure production database? (i.e where would I find this information?)  Thanks for any assistance you can provide.


    I will keep trying...


    DevBox  
    Wednesday, March 21, 2012 3:11 PM
  • Update:  still trying...

    From the following article (http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-firewall.aspx):

    "It may be more convenient for customers to allow TCP/1433 connections to the range of Microsoft’s public network addresses instead of a specific SQL Azure location. This will allow for greater flexibility; as SQL Azure adds more locations, the need to revisit customer access control lists will be reduced.

    For example, the tabular Data Stream (TDS) protocol endpoint for the SQL Azure North Central US data center is data.ch1-1.database.windows.net, with an IP address of 207.46.203.22. Performing a lookup of http://ws.arin.net/whois/?queryinput=207.46.203.22   reveals Microsoft’s entire scope for this IP is 207.46.0.0/16."

    I tried entering the following ip range as a firewall rule: MS North Central US  207.46.0.0 to 207.46.255.255

    (Did not seem to solve my issue of not connecting to the SQL Azure database from my ASP.NET web application).

    DevBox

    Thursday, March 22, 2012 5:51 PM
  • Update:  still trying...

    In the Windows Azure management portal.  "Under Hosted, Service, Storage Accounts & CDN", I looked up the Input endpoints  of my To Do List application and tried entering them as a Firewall Rule in my SQLAzure database.  Unfortunately, this did not fix the issue.

    DevBox

    Thursday, March 22, 2012 7:52 PM
  • Update:  Got the tutorial to work on a different computer and fresh 64 bit configuration, pointing to a different data center.

    I got my hands on a new laptop with 64bit Windows 7 Home Premium , and installed Visual Web Developer 2010 Express, all of the most recent Azure and MVC SDKs.  Seems like what I thought was a firewall issue, could have possibly been issues with database connectivity at the North Central data center.  (I'm speculating, and will still need to test my original configuration against the South Central data center to see if this is actually the case.  But, the North Central data center was not an available choice for hosting a SQL Azure database (3 month subscription), and here's a link to a discussion of this on another thread:

    http://social.msdn.microsoft.com/Forums/da-DK/ssdsgetstarted/thread/7b181eef-ccd1-4090-80d1-0853059d166f

    Tuesday, March 27, 2012 1:20 AM