Solved

Connection string to remote SQL Server 2005

Posted on 2013-12-21
12
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 23

Accepted Solution

by:
Patrick Bogers 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 23

Expert Comment

by:Patrick Bogers
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 70

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 23

Expert Comment

by:Patrick Bogers
ID: 39733959
Great you got the bugger! happy Christmas to you too.
0
 
LVL 70

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
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

632 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