• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

Connection string to remote SQL Server 2005

Hello,
I have a c# application which has been under development for a while and for this purposed is connected to a MS SQL Express 2010 database on the development PC. This has worked well but now I need to move to the next stage and connect to a remote SQL Standard 2005 server database. The connection string that I have used until now has been:

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL  Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Rulesdb2.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />


I have created the same database on the remote SQL 2005 server and now I need to modify the connection string to point to this database. I have gone to www.connectionstring.com but whilst there is a lot of information there, I am still unsure as to the connection string I should be using.

The database that I am trying to connect to has 2 instances:

MYSERVERNAME \ LIVESQLSERVER
MYSERVERNAME \ TESTSQLSERVER

My database RULESDB2 exists on C;\Program Files\ Microsoft SQL Server\MSSQL\DATA.  I have a userId and password available. Can someone please tell me what the format of the connections string should be ?

thanks
Pat
0
pclarke7
Asked:
pclarke7
  • 5
  • 3
  • 2
  • +1
3 Solutions
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

Something like this?

NameOffConnectionString" connectionString="Data Source=MYSERVERNAME;Initial Catalog=LIVESQLSERVER;user id=SQLUserName;password=xxxxxx;MultipleActiveResultSets=True;App=NameOfApplication" providerName="System.Data.SqlClient"
0
 
pclarke7Author Commented:
Hi Patrick,
I'm having problems with the bit below:

App=NameOfApplication" providerName="System.Data.SqlClient"

Is NameOfApplication a key word or something that I need to provide
Are the " correct ? and should there be a ; after NameOfApplication

regards
Pat
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

I forgot to include the addname. It should look like:

<add name="NameOffConnectionString" connectionString="Data Source=MYSERVERNAME;Initial Catalog=LIVESQLSERVER;user id=SQLUserName;password=xxxxxx;MultipleActiveResultSets=True;App=NameOfApplication" providerName="System.Data.SqlClient"  />

Now the quotes match up and no, you dont need to close it with ;
NameOfApplication is used, to my knowledge, to identify the process in SSMS.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That would not work, and also contains a lot of (yet) unnecessary stuff.
connectionString="Server=MYSERVERNAME\LIVESQLSERVER; Database=RULESDB2; User ID=SQLUserName; Password=SQLUserPwd;"

Open in new window

Note that you can use Server or Data Source, and Database or Initial Catalog, as those are synonyms.
Further, since you don't attach the DB on connect, as you did with your SQLExpress instance, you do not provide a path to the DB. The DB already has to be attached to the SQL Server instance you connect to.

The application name is optional. If you don't provide one, the application's binary file name will be used, and that is sufficient in most cases to identify the session in e.g. SSMS.
0
 
Dave BaldwinFixer of ProblemsCommented:
If you are going to connect with the instance name, MYSERVERNAME\LIVESQLSERVER , the SQLBrowser service (sqlbroswer.exe) must be running on the computer with the SQL Server.  You can connect by IP address without it.
0
 
pclarke7Author Commented:
Hi all
this is the status to date.

Using the following connection string:

    <add name="LIVEODBC" connectionString="Server=MYSERVERNAME\TESTSQLSERVER; Database=RULESDB2; User Id=MYUSERID;  Password=MYPASSWORD;"
     providerName="System.Data.SqlClient" />


Getting error message:

Login failed for user MYUSERID


I can log on to this server manually with this User Id and password. When I do I am prompted to selected LIVESQLSERVER or TESTSQLSERVER. When I select TESTSQLSERVER I can access the RULESDB2 database.

RULESDB2 is attached & SQLBrowser Service is running.

Any idea where I am going wrong ?

regards
Pat
0
 
pclarke7Author Commented:
Got it sorted - Thanks to everyone who contributed. The problem was sorted when I added Integrated Security=True; which  sets the connection to use the windows authentication instead of the sql authentication.

Thanks to all and happy Christmas
0
 
Patrick BogersDatacenter platform engineer LindowsCommented:
Great you got the bugger! happy Christmas to you too.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, I have to object. The accepted answers don't have anything to do with your solution, which again is in contradiction to what you told us in the question. And the connection string you posted last looks pretty much like the one I posted, but didn't receive points.
The first answer was really far off, and not worth getting (much) points.

If you provide a SQL user, you can't enable Integrated Security, and vice versa, but you explicitly told us you see the DB when connecting with that user in SSMS, which means you are not using Integrated Security/Windows Logon.

Please explain why you closed the question that way.
0
 
pclarke7Author Commented:
Hi Qlemo,
sorry that you disagree. However the whole point of this website (for me) is about information sharing. I value everyone's input good or bad , correct or incorrect. I am just delighted that there are people out there who have expertise in an area that I am struggling in, and are willing to share that expertise and give up their time to do that. My intention was to divide the point equally amongst all the contributers. I have just noticed that your comments did not receive an points. My apologies for this as this was not intentional - just me messing up when allocating the points.

regards
Pat
0
 
pclarke7Author Commented:
Just a re-distribution of the points to include all contributors. Again, thanks to everyone and I am delighted that I am back up and running again.

regards
Pat
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now