What's My Password?

[Jonathan Wood]

Grrr...

I've spent the last several hours trying to learn ADO.NET. Specifically, the
database connection stuff.

I finally managed to create a database and enter some data within the VS
IDE. I have several ADO.NET books but not one could tell me how to set up my
password.

To make a long story short, I ended up setting up a connection using
(local)\SQLEXPRESS since that's what the only example I could find uses and
I have no idea what to enter for regular MS SQL.

But I didn't have a change to enter a password or anything like that. Based
on another book, I tried all of the following:

string connString = "Data Source=localhost;Initial
Catalog=BlackBeltCoder;Integrated Security=True";
string connString = "Data Source=localhost;Initial
Catalog=BlackBeltCoder;Integrated Security=SSPI";
string connString = "Data Source=localhost;Initial
Catalog=BlackBeltCoder;user id=sa;password=opensesame";

SqlConnection conn = new SqlConnection(connString);
conn.Open();

In each case, the code fails on the call to Open(). Here's the message I
get:

"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)"

Well, that's very helpful. I'm not using a remote connection.

I'm running my Web site in the IDE and will still need to figure out how to
connect to the database once I copy the site to my Web hosting account but I
can only deal with so much pain at once.

Is there any help available to figure out how to connect to the database I
have connected?

 

[RobinS]

Are you trying to connect to SQLServer? Or SQLServer Express?

Robin S.

 

[Cor Ligthert [MVP]]

Johanthan,

We all try to learn on this board, will you next time the name of books you
have used as well.

Thank you for informing us that we have to watch this when we buy books
about AdoNet.

Cor

 

[Miha Markic [MVP C#]]

Hi Jonatahan,

Well, it depends on some factors.
1. Do you wanna use integrated security (credentials that process is running
under). More secure and recommened as you don't have to store password
anywhere.
2. Do you want to use sql authentication. Less secure as you have to provide
username and password.
3. If 2 then you have to make sure your sql express is configured to allow
sql authentication.
4. If 1 then you have to give sql server privileges to the account under
which your asp.net application is running.

Here is a good article on the topic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch05.asp

Also be aware that default asp.net processes on 2003 uses different
credentials than the one on xp.

HTH

 

[William \(Bill\) Vaughn]

I hope it was not my book that lead you astray. Getting connected to SQL
Express can be a bit tricky... it's born deaf and blind. See my blog for a
whitepaper on connecting issues and perhaps my book (which has a long
chapter on connecting) will help...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jonathan Wood]

I'm a long time programmer who has not done much database programming (I've
worked with Access a bit and did one write a Visual Basic application that
used the Jet database engine many years ago).

I don't know which of the many various technical options I "want". I simply
want to be able to create a database and then access it from my Web
application (written using the full version of Visual Studio 2005) and have
it reasonably secure. If my code needs a user name and password then that's
just fine as long as I have some control over what they are.

At this point, it would be nice just to be able to start writing a little
bit of database code that works even if it requires Integrated Giblet Widget
Security technology.

I'll take a look at the article you referenced. Unfortunately, I'm not
hopeful it will answer my questions at this point.

Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Hi Jonatahan,

Well, it depends on some factors.
1. Do you wanna use integrated security (credentials that process is
running under). More secure and recommened as you don't have to store
password anywhere.
2. Do you want to use sql authentication. Less secure as you have to
provide username and password.
3. If 2 then you have to make sure your sql express is configured to allow
sql authentication.
4. If 1 then you have to give sql server privileges to the account under
which your asp.net application is running.

Here is a good article on the topic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch05.asp

Also be aware that default asp.net processes on 2003 uses different
credentials than the one on xp.

HTH
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Grrr...

I've spent the last several hours trying to learn ADO.NET. Specifically,
the database connection stuff.

I finally managed to create a database and enter some data within the VS
IDE. I have several ADO.NET books but not one could tell me how to set up
my password.

To make a long story short, I ended up setting up a connection using
(local)\SQLEXPRESS since that's what the only example I could find uses
and I have no idea what to enter for regular MS SQL.

But I didn't have a change to enter a password or anything like that.
Based on another book, I tried all of the following:

string connString = "Data Source=localhost;Initial
Catalog=BlackBeltCoder;Integrated Security=True";
string connString = "Data Source=localhost;Initial
Catalog=BlackBeltCoder;Integrated Security=SSPI";
string connString = "Data Source=localhost;Initial
Catalog=BlackBeltCoder;user id=sa;password=opensesame";

SqlConnection conn = new SqlConnection(connString);
conn.Open();

In each case, the code fails on the call to Open(). Here's the message I
get:

"An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open
a connection to SQL Server)"

Well, that's very helpful. I'm not using a remote connection.

I'm running my Web site in the IDE and will still need to figure out how
to connect to the database once I copy the site to my Web hosting account
but I can only deal with so much pain at once.

Is there any help available to figure out how to connect to the database
I have connected?

 

[Jonathan Wood]

Good question.

 

[RobinS]

I hardly think it's the fault of the books on ADO. I can
understand how they would think you know your own username
and password in SQLServer.

Now , if it was a book on SQLServer, that might be a
different issue.

Robin S.

 

[Jonathan Wood]

Cor,

We all try to learn on this board, will you next time the name of books
you have used as well.

Are you asking for the name of the books?

Well, the two I have in front of my are:

[Microsoft ADO.NET 2.0 Step by Step (Rebecca M. Riordan, MS Press)]
This book provides step by step instructions on creating my database
connection. It starts by telling me to choose the Add New Data Source from
the Data menu. That's very nice. When I read it, I had no Data menu. After
much messing around, I did get into an area where a Data menu appeared.
Unfortunately, it had no Add New Data Source command.

I managed to get the data window on the left side of the screen and there
were other options but none looked like the dialog in the book. But I
eventually worked through them. Several times, I was asked if I wanted
Windows Authentication or SQL Server Authentication. Well, the book wasn't
much help at all there but since this will be a Web application, I thought I
might want to enter a user name and password under SQL Server
Authentication. Well, that's very nice too but it then told me the user name
and password were not valid. (Perhaps I have to drive to Microsoft to
specify what will or will not be valid passwords in my program.)

At one point I did get a dialog box that looks closer to what is in the book
and the book says the following about the Server Name box:

"In the Server Name box, type (local)SQLEXPRESS. The local portion indicates
the local computer, and the SQLEXPRESS is the SQL Server instance name.
IMPORTANT: This book and code samples have been designed for use with a
default installation of Microsoft SQL Server 2005 Express Edition, which has
an instance name of SQLEXPRESS. If you want to use this book with a
different SQL Server instance name or with the full version of SQL Server,
you will have to adjust the exercise steps and connection strings
accordingly."

Well, that's nice too. Apparently, this books recommendation about the
server name field on the regular version of Visual Studio is TO BUY ANOTHER
BOOK!

[Pro ASP.NET 2.0 in C# 2005 (McDonald/Szpuszta)]
In all fairness, this is a large book and I've spent limited time with it.
But I see nothing in the ADO.NET Fundamentals chapter that tells me what I
need to know. It does offer several different types of connection strings,
which I listed in my original post. None of them work for me. That chapter
then jumps right into writing the code without mentioning how to create a
database from the IDE.

Thank you for informing us that we have to watch this when we buy books
about AdoNet.

Well, after several replies no one here has provided that specific
information either. Obviously, there's a bit of a hurdle to get from where I
am to where I'm writing code to work with a database I have created. And
it's very frustrating as it seems it could've been made far easier.

 

[RobinS]

I'm not sure what that means. Does that mean you don't know?
You said in another post you were trying to figure out
which technical option you wanted.

The reason I asked is because I had this problem with SQLServer
running on my laptop, and figured out how to fix it. I don't
know if it will fix the problem in SQLServer Express, though.

The problem is that the database is not set to accept remote
connections. I searched MSDN on the exact error message, and
found only one article about it, and it fixed my problem.
(THAT's a first!)

I opened the Surface Area Configuration utility,
selected Services & Connections,
clicked on Remote Connections under Database Engine,
selected "Local and Remote connections"
and set "Using both TCP/IP and Named Pipes" to true.

How you get there from SQLServer Express, I don't know.

I usually use integrated security, which means I use the
Windows username and password. This means there is no password
hardcoded anywhere that someone can find.

I hope this helps in some way.
Robin S.
-------------------------------------

 

[Jonathan Wood]

William,

I hope it was not my book that lead you astray. Getting connected to SQL
Express can be a bit tricky...

Yeah, particularly when you're not using SQL Expres (I think).

it's born deaf and blind. See my blog for a whitepaper on connecting
issues and perhaps my book (which has a long chapter on connecting) will
help...

I must admit, I'm getting a bit pessimistic about that. Especially, when I
have several books and I'm not any closer to connecting to my database even
after getting several replies from this group.

Thanks.

 

[Jonathan Wood]

Robin,

I'm not sure what that means. Does that mean you don't know?
You said in another post you were trying to figure out
which technical option you wanted.

Yes, it means that I do not know. And, yes, I'm trying to figure out which
technical options I "want".

The reason I asked is because I had this problem with SQLServer
running on my laptop, and figured out how to fix it. I don't
know if it will fix the problem in SQLServer Express, though.

I doubt it. My issue is more of a (which should be) basic issues I'm trying
to learn, and not more of a problem with a particular computer.

The problem is that the database is not set to accept remote
connections. I searched MSDN on the exact error message, and
found only one article about it, and it fixed my problem.
(THAT's a first!)

I opened the Surface Area Configuration utility,
selected Services & Connections,
clicked on Remote Connections under Database Engine,
selected "Local and Remote connections"
and set "Using both TCP/IP and Named Pipes" to true.

How you get there from SQLServer Express, I don't know.

I usually use integrated security, which means I use the
Windows username and password. This means there is no password
hardcoded anywhere that someone can find.

I hope this helps in some way.

First off, I have a hunch I'm not using SQL Server Express (based on the
fact that none of the software I installed was an Express Edition) ????

Second, I'm confused by the term remote connection. Isn't that related to
when you connect to a database that is located on another computer?

 

[RobinS]

What do you mean, "I think"? Do you not know if you're using
SQLServer of SQLExpress? Is it on your machine? Did you
install it?

Robin S.
-----------------

 

[Jonathan Wood]

I hardly think it's the fault of the books on ADO. I can
understand how they would think you know your own username
and password in SQLServer.

So is that what it's asking me? The user name and password to my computer?
If so, that would be at least one new thing I've learned. That is not
obvious to me at all. And, of course, begs the question as to what they'll
be on my host server.

 

[Jonathan Wood]

Installing VS 2005 seemed to have taken around 45 minutes. Are you asking me
to remember each and every component that was installed during that process?

 

[William \(Bill\) Vaughn]

Ok, I understand.
When you install any version of Visual Studio (even the Express SKU), it
installs SQL Server Express Edition (quietly). There's no reason you should
have known that. To determine if you have SQL Server Express installed, use
the Server Explorer in Visual Studio and click on Servers | YourSystem |
Services and scroll down to see if "SQL Server (SQLEXPRESS)" is listed. If
it's there, you have an instance of SQL Server (named "SQLEXPRESS")
installed and (possibly) running on your system. This assumes you aren't
running the Express SKU of Visual Studio as this exposes a "database"
explorer instead with far more limited functionality.

When connecting to any instance of SQL Server, you have to establish who you
are and which database on the server (it can support virtually any number of
databases) you want to use. You identify yourself with username and password
credentials. These can either be supplied by Windows authentication (as when
you logged on to your system or on to a domain) or by SQL Server
authentication. By default, only Windows authentication is enabled (on all
SKUs of SQL Server). This means that SQL Server must have a Login account
setup to permit access to the server. By default, SQL Server permits members
of the Administrators group to be granted access to the server. If you don't
have a Username and Password in your ConnectionString, you must have
"Trusted Connection=Yes" or "Integrated Security=SSPI" to indicate that SQL
Server is to use the current user's Windows authentication credentials.
However, when creating an application, you can't assume that the end-user
will have those rights.

I discuss how to configure the server and all of this in far more detail in
my latest book--See Chapter 9.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[RobinS]

If you installed it, and didn't pay big money for it, it's
probably SQLServer Express. I believe when you install Visual
Studio 2005, it gives you the option to install SQLServer Express.
So the big question is how did you install it, and do you
have the original media?

Also, if you have the real deal, you can do Start/Programs/
MicrosoftSQLServer2005/... and you'll have a bunch of options.

As for the remote connection thing, I don't understand it
either, because I'm running the real deal on my laptop
along with VS2005, so I don't think of that as "remote",
but apparently SQLServer does. Maybe it thinks of remote
connections as anything connecting to the server, rather
than running queries by running something inside SQLServer
from the Query Analyzer or the Management Studio. That would
be my guess.

Robin S.
--------------------

 

[Jonathan Wood]

William,

When you install any version of Visual Studio (even the Express SKU), it
installs SQL Server Express Edition (quietly). There's no reason you
should have known that.

Yes, thank you!

To determine if you have SQL Server Express installed, use the Server
Explorer in Visual Studio and click on Servers | YourSystem | Services and
scroll down to see if "SQL Server (SQLEXPRESS)" is listed. If it's there,
you have an instance of SQL Server (named "SQLEXPRESS") installed and
(possibly) running on your system. This assumes you aren't running the
Express SKU of Visual Studio as this exposes a "database" explorer instead
with far more limited functionality.

Yes, there is an entry named "SQL Server (SQLEXPRESS)".

Suddenly, I feel I'm actually moving forward. However, there are two issues
in my mind. 1) It appears I have SQL Server Express, but I'm not as sure
that's the version I want to use. And 2) I will eventually move this to my
Web server (currently hosted by GoDaddy.com) and assume I will need to use
whatever they have.

When connecting to any instance of SQL Server, you have to establish who
you are and which database on the server (it can support virtually any
number of databases) you want to use. You identify yourself with username
and password credentials. These can either be supplied by Windows
authentication (as when you logged on to your system or on to a domain) or
by SQL Server authentication. By default, only Windows authentication is
enabled (on all SKUs of SQL Server). This means that SQL Server must have
a Login account setup to permit access to the server. By default, SQL
Server permits members of the Administrators group to be granted access to
the server. If you don't have a Username and Password in your
ConnectionString, you must have "Trusted Connection=Yes" or "Integrated
Security=SSPI" to indicate that SQL Server is to use the current user's
Windows authentication credentials. However, when creating an application,
you can't assume that the end-user will have those rights.

To reiterate, this is a Web site I'm developing (although I do plan to
develop some desktop applications in the future that will use a database).
Here are the connection strings I've tried to far:

1. "Data Source=localhost;Initial Catalog=BlackBeltCoder;Integrated
Security=True"
2. "Data Source=localhost;Initial Catalog=BlackBeltCoder;Integrated
Security=SSPI"
3. "Data Source=localhost;Initial Catalog=BlackBeltCoder;user
id=sa;password=opensesame"

In all cases, the following code fails:

SqlConnection conn = new SqlConnection(connString);
conn.Open();

I just get a message about not being configured for remote connections,
which I don't understand since (at least, so far) it would seem to be a
local connection.

I discuss how to configure the server and all of this in far more detail
in my latest book--See Chapter 9.

Well, perhaps I should look out for that then.

Thanks.

 

[Jonathan Wood]

Robin,

If you installed it, and didn't pay big money for it, it's
probably SQLServer Express. I believe when you install Visual
Studio 2005, it gives you the option to install SQLServer Express.
So the big question is how did you install it, and do you
have the original media?

I simply cannot believe there is no way to tell the difference without
remembering every option selected during the initial installation. All I
remember is that I had to install some SQL server software for VS 2003, and
that I read it was not needed when I installed VS 2005. Either way, I am not
going to be able to tell you which installation options were selected
exactly.

Also, if you have the real deal, you can do Start/Programs/
MicrosoftSQLServer2005/... and you'll have a bunch of options.

I do have a Microsoft SQL Server 2005 options in my All Programs menu.

As for the remote connection thing, I don't understand it
either, because I'm running the real deal on my laptop
along with VS2005, so I don't think of that as "remote",
but apparently SQLServer does. Maybe it thinks of remote
connections as anything connecting to the server, rather
than running queries by running something inside SQLServer
from the Query Analyzer or the Management Studio. That would
be my guess.

Sounds right. Now all I need to do is figure out what to do about it.

 

[RobinS]

Where is it asking you for the username and password? Because
if I use integrated security, it doesn't ask me, it collects
that information from Windows.

Here's an example of connection string I'm using:

Data Source=xMyMachineName\xMySQLServerName;Initial
Catalog=myFavoriteDB;Integrated Security=True

If you have SQLServerExpress installed, the default
name is the computer name with \SQLExpress appended to it.
To find your computer name, right-click on My Computer
and choose the tab "Computer Name".

To create a SQLServer database using Server Explorer in VS2005:

Click on View/ServerExplorer.

Right-click on the Data Connections node and select
Create New SQL Server Database from the context menu.
This displays the Create New SQL Server Database dialog.

Select the name of the SQL Server you want to use.
You may not see your SQLServer Express edition appear
in the list. If yot, you can type it in as <your machine
name>\SQLExpress or as (local)\SQLExpress or just .\SQLExpress.

Define the appropriate info to log into the selected SQL Server.
By default, SQL Server Express is installed with Windows
Authenticaion in place.

Define the name of the database.

Click <OK>.

After it's in the Server Explorer, you can create tables
in the database, add data, create stored procedures, etc.

Does this work for you?

Robin S.