Azure SQL Database connection issue from Web Role

0
votes

I have the following problem when trying to connect from my Web Role instance to the Azure SQL Database:

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

My connection string:

metadata=res:///MyModel.csdl|res:///MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string='data source=SOMETHING.database.windows.net,1433;initial catalog=DBNAME;User ID=USERNAME@SOMETHING;Password=PASS!;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;'

I have also linked the database with my Web Role and added the allowable IP. I can connect from using SQL Server Management Studio.

Could there be another problem here?

1
.netazureconnection-stringazure-sql-databaseazure-web-roles
Did you check your allowed ip addresses list in the portal? – haim770
This didn't work. But now it appears that it won't connect in my emulator either. But it connects fine if I run my web application project. – Dave New
Are there any entries in your allowd ip addresses? – haim770
Yes, my IP and the IP of the web role. I can connect to the database fine in SQL Server Management Studio – Dave New
I'm with haim770. Can you try adding a rule that opens up 0.0.0.0 to 255.255.255.255 AND be sure the Windows Azure Services is set to allowed. See if your web role can see it then. After validation, remove this rule. – Dennis Burton

1 Answers

0
votes

I had to add MultipleActiveResultSets=True into the connection string:

metadata=res:///MyModel.csdl|res:///MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string='data source=SERVER.database.windows.net,1433;initial catalog=DBNAME;User ID=USER@SERVER;Password=PASS;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;MultipleActiveResultSets=True'