Solved

Connection string to remote SQL Server 2005

Posted on 2013-12-21
12
460 Views
Last Modified: 2013-12-22
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
Comment
Question by:pclarke7
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 19

Accepted Solution

by:
Patricksr1972 earned 167 total points
ID: 39733561
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
 

Author Comment

by:pclarke7
ID: 39733591
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
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39733601
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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 167 total points
ID: 39733657
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
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 166 total points
ID: 39733760
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:pclarke7
ID: 39733924
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
 

Author Comment

by:pclarke7
ID: 39733949
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
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39733959
Great you got the bugger! happy Christmas to you too.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39734084
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
 

Author Comment

by:pclarke7
ID: 39734544
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
 

Author Closing Comment

by:pclarke7
ID: 39734680
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now