Connection String Problem

Question

Connection String Problem

versmn23

SSC-Addicted

Points: 460
More actions
February 5, 2010 at 12:52 pm

#216345

I've been having a problem with connecting to a database I have installed on my laptop. The calling function is a VS 2008 pro. c#.Net app. that I'm writing to get some DB experience. I have used several different formats of connection string to try to access the Pubs DB (gotten from connectionstrings.com) and none have worked, and I'm getting a SqlException thrown when my connection tries to open. I have tracked the execution to that point using the VS debugger, but can't really see anything useful in the stack trace of the exception to tell me which part of the connection string that it does not like. Below are the variations that I have tried so far which were unsuccessfull:
//string connectionString =
// "Data Source=SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";
string connectionString =
"Data Source=HENRY-PC\SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";
The actual instance name of SQL Server that I'm trying to access is SQLSRVDEVNSMAIN and the actual server name is
HENRY-PC\SQLSRVDEVNSMAIN. I never setup a specific username or password for SQL Server when I installed it. I would very much appreciate any suggestions for how to figure out the problem(s) ASAP. Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 1

Don't know the answer to your specific question but I always create and test the connection strings by using the following method - it might help you.

1. Create a file on your desktop called conn.udl

2. Double-click on the file

3. This opens up a data link properties app where you can enter connection details and test it

4. Exit the app

5. Open the conn.udl file in notepad

6. Copy the connection string

Lowell SSC Guru Points: 323518 More actions · Answer 2

just wrap the servername in brackets, the same way you do with TSQL reserved words:

Data Source=SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";

string connectionString =

"Data Source=[HENRY-PC\SQLSRVDEVNSMAIN];Database=pubs;Trusted_Connection=True;";

you could also switch to an ip\instance, or an ip port, which you might have to find first.

Data Source=SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";

string connectionString =

"Data Source=192.168.1.200\SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";

Data Source=SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";

string connectionString =

"Data Source=192.168.1.200,1723;Database=pubs;Trusted_Connection=True;";

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

versmn23 SSC-Addicted Points: 460 More actions · Answer 3

I tried putting the data source name in square brackets as you suggested, but it didn't help and I got the same SqlException as before. I don't know all that much about naming instances of Sql Server 2005 when you install it, but is the backslash that I have in the server name of this instance of SqlServer maybe causing the problem? I ask because when I try to put the whole server name in as the data source (as in "HENRY-PC\SQLSRVDEVNSMAIN") value in the connection string I'm getting a compile error on the "S" right after the backslash which says "Unrecognized escape sequence". I know how to get rid of this error and have and I'm still getting the SqlException. So then that leads me to ask for any suggestions on how to more information out of the exception to give me more of an indication of what I'm coding in the connection string that is wrong. I'd appreciate anything you can offer. Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 4

it sounds like you are developing in C# becasue of the error on the escape;, i think you need to use double slash to escape the string in that case:

Data Source=SQLSRVDEVNSMAIN;Database=pubs;Trusted_Connection=True;";

string connectionString =

"Data Source=[HENRY-PC\\SQLSRVDEVNSMAIN];Database=pubs;Trusted_Connection=True;";

see if that works for you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

This is what I use, and have no problems at all. I use the web.config file to store the connection string. I encrypt it normally.

<connectionStrings>

<add name="<WhatEverName>" connectionString="Data Source=<ServerName>\<InstanceName>;Initial Catalog=<DatabaseName>;user id=<SQLUSerName>;password=<Password>;" providerName="System.Data.SqlClient"/>

</connectionStrings>

Replace everything in the tags with your info. I would also suggest using a SQL Login, and assigning no permissions. If you are not using Active Directory, you should use a SQL Login. You grant Execute only to the stored procedures. Do not use inline ad-hoc queries.

Andrew SQLDBA

versmn23 SSC-Addicted Points: 460 More actions · Answer 6

The application I'm developing is written in C#, but its not a web application so I'm assuming I would need to put the connection string itself into an app.config file which I currently don't have, somewhere in my project. Correct? If so then how would I reference it in the program so that the program will find it? Please advise. Thanks. Henry

versmn23 SSC-Addicted Points: 460 More actions · Answer 7

I currently have the Windows Authentication set as the authentication method, with a default username and no password. I tried earlier to change it to Sql authentication but I think to change it I will need to do so when logged in as administrator. If I change the authentication method to Sql authentication ad then specify a username and password will that be more likely to work than the way its currently setup? Please advise and thanks.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 8

Yes, it should work better then Windows Auth, since you do not have Active Directory(I am assuming). Using SQL Auth, it more simple when you are coding something like that. You can always change it in the app.config file. The app will not know the difference. You are only creating a reference to the alias that you use in the connection string.

Here is a a great example of creating and adding a connection string to the app.config file. If you do not have one, simply create the file in the root of the app.

http://www.dotnetspider.com/forum/ViewForum.aspx?ForumId=33660

Andrew SQLDBA

versmn23 SSC-Addicted Points: 460 More actions · Answer 9

Thanks for the help. I will try and add it to the app.config file based on the link you suggested. Can I just use the following to retrieve it then from the file so I can use it in my application:

string connectionString =

ConfigurationManager.AppSettings["connectionString"];

And then plug it into my code as I'm using it now, which does work?