C# application accessing remote sql server 2008 instance ... not working

Question

I have a console app that needs to connect to a remote sql server 2008 instance.... this particular line throws an error even though i have access to that database... connection.open() is the line that is throwing the error...

Answer 1

Make sure the connection string in you console app uses the same credentials and settings that you use to successfully connect in management studio.

Are you using Windows authentication in one place and a SQL login in another?

Answer 2

Have you been able to connect to this remote server before? If you have been able to connect previously, but cannot do so now, that may be an indication that a backup copy of the database, from another server, has been restored to that server.

When you write that you "have access" to that database, how do you connect? Close your copy of SQL Server Management Studio and re-open it; when you get the Connect dialog, be sure to enter the user credentials that your console app uses to connect. Do you see your database? Are you able to execute stored procedures, run SELECT statements, or otherwise do what you would expect?

If you do not see the database--or if you cannot do the activities you expect, you have probably restored a database from another server. This is very common--a database developer is working on the next revision of the database on his development machine; when it comes time to deploy he backs up the database, copies it to the server, and restores the database. Now he can't connect--and can't understand why.

The reason is that the Login and User are presented in the SSMS UI as strings--but in fact they are integer IDs. You will have to do a bit of scripting to delete the invalid User record from the database in order to assign it to the new server's Login.

If this sounds like your problem, respond--I'll check back in a bit, and follow up with more help.

JM

Answer 3

Make sure that Sql server 2008 instance available at your system where you are running your application. Atleast client version of Sql server 2008 should be installed on development machine. Check connection string and server system also allow network connection.