0

I developed this application on my local computer using VS2012 and SQL 2008R2. I am trying to move it onto production server. It is a virtual server with 2012 SQL Server. I checked all the properties for remote connection and everything is enabled. The application opens with the login screen and once I enter the username and password (this was created on my computer using WSAT. Now I get this error: 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: 25 - Connection string is not valid) This is what I have in web.config:

<connectionStrings>
    <remove name="LocalSqlServer" />
<add connectionString="Server=\\events;Database=Digital Signage;Integrated Security=true" name="DSConnectionString" />
    <add connectionString="data source=\\events;Integrated Security=SSPI;AttachDBFilename=c:\\DS\aspnetdb.mdf;User Instance=true" name="LocalSqlServer" providerName="System.Data.SqlClient" />

Nita
  • 195
  • 3
  • 8
  • 20

1 Answers1

1

Not entirely sure, but some points which may help you troubleshoot:

  • You can use a resource like this to help you generate the appropriate connection string
  • I am not sure that Server=\\events sounds entirely right - you should perhaps replace this with the actual SQL instance name here when deploying remotely (either <machine name> or <machine name>\<instance name> for a named instance)
  • You probably don't need to explicitly state the port which SQL Server is listening on (if it's still the default 1433), but something to keep in mind
  • Do you have anywhere that you explicitly reference the connection string LocalSqlServer in your code? If so, have you changed that to reference the connection string DSConnectionString before building and deploying? (NB. It may be better to just keep the connection string entry but change the value of the connection string to what you need for the SQL 2012 instance, rather than creating a new connection string entry but I'm not sure about your specific use case)
nkvu
  • 5,551
  • 2
  • 16
  • 12
  • LocalSqlServer is only used for user account database I think (ASPNETDB.dbf), rest of the places I use DSConnectionString to connect to my tables. Since I used 2008R2 in my development and 2012 on the production, is that why I am having trouble. – Nita Apr 29 '13 at 15:32
  • No, I don't think that you shouldn't have a problem between versions. Likely something is just funny in your connection string - I would just use [connectionstrings.com](http://www.connectionstrings.com/sql-server-2012) to try and construct an appropriate connection string for you – nkvu Apr 29 '13 at 16:41
  • Ok I added sql authentication with username and password. Now it says cannot find the database. I removed the LocalSqlServer link from the connection string through IIS. LocalSqlServer is declared in machine.config. – Nita Apr 29 '13 at 17:53
  • Is the SQL Authentication account is defined on the server and does it have permissions to the DB? It might be (unsure) that your database name has a space in it? (I've never dealt with that, myself)...is it possible to rename your database so it does not have a space in it? – nkvu Apr 29 '13 at 17:55
  • Ok I changed the name of the database, finally I had to write the quey. FYI, I don't build applications all the time; first time trying to deploy into production site which is on our own server. My connection string looks like this now: I get this error now (:. – Nita Apr 29 '13 at 19:15
  • Sorry I don't see anything in your comment - you might want to update your original question with the error (e.g. a screenshot) and your revised connection string (please also redact the password if you're using SQL Auth) – nkvu Apr 29 '13 at 19:16
  • I get this error:Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. I don't have any stored procedure – Nita Apr 29 '13 at 19:16
  • Are you using ASP.NET membership in your application (e.g. for user accounts or roles or profiles)? That stored proc is related to that. [This](http://stackoverflow.com/questions/2165908/could-not-find-stored-procedure-dbo-aspnet-checkschemaversion) question should help you resolve your issue – nkvu Apr 29 '13 at 19:19
  • Yes I do and that is the problem. The connection string for "LocalSqlServer". I was so excited to use the membership controls but now it is such a pain to use it on production. I should have just created table and did the old style way :(. I will try to run the exe. Thanks nkvu – Nita Apr 29 '13 at 19:27
  • OK, I was able to run the exe file, now I get this error: the execute permission was denied on the object 'aspnet_checkschemaversion' database schema 'dbo' I can see the the table in sqlserver now, but not sure where to give permission. – Nita Apr 30 '13 at 17:32
  • That's a stored procedure - you will need to give permissions to the SQL account you created. See [this](http://msdn.microsoft.com/en-us/library/ms345484.aspx) for some steps....There should be a whole bunch of stored procedures there for ASP.NET membership which you should probably grant execute access on as well – nkvu Apr 30 '13 at 19:19
  • Upvote for 'You probably don't need to explicitly state the port which SQL Server is listening' . This solved my problem – Rasshme Chawla Dec 18 '15 at 04:32