Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Connection string to remote SQL Server 2005

Posted on 2013-12-21
12
Medium Priority
?
498 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 23

Accepted Solution

by:
Patrick Bogers earned 501 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 501 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 498 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 71

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

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.

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 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