web service - SQL connection string

1
votes

I've built an .asmx web service which retrieves informations from a local sql server 2014 database. Everything is working fine on localhost, but after publishing the web service to Azure i get the error:

An unhandled exception of type 'System.Web.Services.Protocols.SoapException' occurred in System.Web.Services.dll

Additional information: Server was unable to process request. ---> 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

SQL Server is configured to allow remote connections.

My connection string is like:

string con2 = @"Data Source=OfficePc\MSSQLSERVER2014;Initial Catalog=Database;Persist Security Info=True;User ID=Admin;Password=123456";

Is the error the result of something missing from the connection string, or am i missing some configurations changes?

3
c#sql-serverweb-servicesazureconnection
Are you sure this is the right connection string? If you use Visual Studio, you can check it on Server Explorer window for example. – Soner Gönül
Your Azure based web service knows where 'OfficePc\MSSQLSERVER2014' is ? Shouldn't that be an IP or something? – Paul Zahra

3 Answers

1
votes

As Paul mentioned in a comment under the question, your connection string is pointing to a local database resource (presumably on your dev machine). Even though you configured your local database server to support remote connections, the address OfficePc\MSSQLSERVER2014 isn't addressable, as that does not equate to a machine address (IP address).

Your app would need to connect to your database via an accessible IP address (which might require you to do some port-forwarding on your local network, or open ports on your firewall).

Alternatively, you can migrate your database to Azure (either with SQL Server in a VM or with the SQL Database service).

Keep in mind: If you are accessing a local (on-premises) database server from Azure, there will be latency added, as well as some outbound bandwidth costs.

0
votes

It looks like SQL Server instance is not running or not accessible. Try connecting to the same database using SSMS and if you get the same error then the instance is not running.

0
votes

Mostly the error occurs when the Database server was not found. Recheck if the server name (Data Source) is mentioned correctly. If you manually generated the connection string use .uld file to generate connection string.

To auto generate connection string using .udl file:

  1. Create a sampe.txt file.
  2. Rename it as sample.udl file.
  3. Then double click on it, It will show you window entitled 'Data Link Properties'.
  4. Configure the connection there.
  5. Then Test the connection using test connection button.
  6. Then open the file with notepad. It will show you the exact connection string.

enter image description here

For further reference check : MSDN