Solved

Connection string to remote SQL Server 2005

Posted on 2013-12-21
12
467 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

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 83

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
 

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 69

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 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