0

im using .net webservice with a LOCAL database (mdf). this all website worked fine until i moved all the files to another computer as is and created a new application on iis. all the methods on this webservice working just fine, just the methods that need to connect and query the database i get the following error:

System.Data.SqlClient.SqlException: 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)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
   at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
   at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.Table`1.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.ElementAt[TSource](IQueryable`1 source, Int32 index)

ive change the web config connection string couple of times and there is no firewall protection.

thanks...

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Gladi
  • 415
  • 4
  • 16
  • A `.mdf` file is a SQL Server database - you cannot just move the file - you still need to have SQL Server (Express or other edition) installed on the target machine ... – marc_s Nov 27 '12 at 12:18

3 Answers3

3

If you are using a MDF file, there must a SQL Server or SQL Server Express installed on your original machine.

As you say that you had moved your files to another computer, your configuration obviously do not work anymore. You will have to adjust all path information to fit to the new installation folders.

Also, you will have to make sure that there is a running SQL Server or SQL Server Express instance on the new computer AND make it point to the MDF database file.

Jens H
  • 4,590
  • 2
  • 25
  • 35
  • i just created website project on visual studio and attached it to the database. what paths i need to change except the connection string? – Gladi Nov 27 '12 at 12:43
  • in my other computer i just run and configure from the iis. nothing with sql server definitions. – Gladi Nov 27 '12 at 12:43
  • @DanielGabay: you **must have** SQL Server Express installed on that machine. – marc_s Nov 27 '12 at 12:58
  • for my needs, what version i need to install? – Gladi Nov 27 '12 at 13:14
  • You need the same version you had on the other machine. – John Saunders Nov 27 '12 at 13:25
  • i did this and now i get this error: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. – Gladi Nov 27 '12 at 14:11
3

MDF is the standard SQL Server extension, and you are using SqlClient to connect, so you are definitely using SQL Server, but you're connecting to a local database (MDF) file rather than an actual SQL Server Service.

  • Ensure that the account that your web application is running under has access to the MDF file.

  • Make absolutely certain the connection string is connect. At least post it here so we can help.

  • To start troubleshooting, temporarily give the Everyone group access to the MDF file and see if that helps.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • my connection string: in other computer it worked just fine... maby its an iis issue? – Gladi Nov 27 '12 at 12:44
  • 1
    This connection string connects to a SQL Server, not an MDF file. It tries to connect to a SQL Server installed on the same server as your web server, with an instance name of SQLExpress. I'm guessing this is not the case. You need to work out whether you want to connect to a SQL Server or just an MDF File. Here is a sample of a connection string connecting to an MDF file: http://stackoverflow.com/questions/928813/connection-string-to-connect-to-mdf – Nick.Mc Nov 27 '12 at 22:09
1

I would suggest a couple of infrastructure checks, sometimes is better than just focusing on the code:

  1. Ensure your SQL server has proper LAN connection
  2. Make a ping from the server hosting your web service to the the server hosting your SQL Server
  3. Use the MAnagement Studio to query for database objects from a different server (may be possible that a network administrator blocked important ports inadvertently)

If possible check twice your connection string, sometimes a semicolon can ruin the whole thing.

EDIT

Connection String

<add name="gatewayConnectionString" 
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=c:\Projects\p\file.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" 
providerName="System.Data.SqlClient"/>

Hope it helps,

Jorge Alvarado
  • 2,664
  • 22
  • 33
  • hi jorge, thanks for the answer. im not using sql server. its just an asp application that query a local db with linq. – Gladi Nov 27 '12 at 12:12
  • I see, sorry for that, updated the post for better visibility – Jorge Alvarado Nov 27 '12 at 12:15
  • my connection string: in other computer it worked just fine... maby its an iis issue? – Gladi Nov 27 '12 at 12:36
  • you are using the wrong connection string, look at my example with AttachedDBFileName – Jorge Alvarado Nov 27 '12 at 12:42
  • @Daniel the way I see it, you are not setting the file path of the database, when you changed the database to another computer your file system structured has certainly changed, this is why you need to update it, can you post the connection string with your updates again? – Jorge Alvarado Nov 27 '12 at 12:52
  • this is not right : Initial Catalog=gateway that is used when you are connecting to another server, that is why you need to give AttachedDbFileName to the exact path (including drive and extension file) – Jorge Alvarado Nov 27 '12 at 12:53
  • this is my current connectionstring: – Gladi Nov 27 '12 at 13:00
  • let's try one more time buddy, can you update the webconfig with the new code I edited? – Jorge Alvarado Nov 27 '12 at 13:19
  • its still the same... im trying to install sql server express. – Gladi Nov 27 '12 at 14:02
  • i did this and now i get this error: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. – Gladi Nov 27 '12 at 14:09