Which Method to Create a Database Do I Use?

[Jonathan Wood]

It appears there are two methods for creating a database as part of an ASP
project.

I can use the Server Explorer and create a database that I can't easily see
as a file, and create a connection. On the other hand, I can right click in
the Solution Explorer window and select Add New Item, and then create an SQL
Database. This creates an MDF file directly.

I'm having trouble getting up to speed on the database stuff and have two
seemingly unrelated ways to create my database makes it all the more
confusing.

Can anyone explain the difference between these two methods, and then help
me understand which is the best method for creating a database to be used by
my Web page?

Thanks!

 

[William \(Bill\) Vaughn]

Either method is acceptable. They both create a database .mdf file that can
be populated with appropriate schema. As a matter of fact, there are
probably a half-dozen ways to create a database and populate it with schema.
The fact that you're going to use it in an ASP project suggests that the SQL
Server engine be located on another system in a heavily used production
application, but for most other installations it does not make much
difference.

I discuss how to decide in depth in my book.

--
____________________________________
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]

Bill,

I don't know why this is so infuriatingly difficult. I would say I get a
successful database connection maybe one out of every hundred attempts. And
the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this starts
making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server Explorer, it
appears to create a DBO file instead of an MDF file. There seems to be other
differences as well. I've been programming for a hundred years (almost) but
feel completely useless right now.)

 

[William \(Bill\) Vaughn]

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object (the
database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found on my
blog. I also devote an entire chapter to the subject in my book. Yes, it can
be very frustrating until you get a feel for how things work. Lots of folks
have figured it out, even old guys like me...

hth

--
____________________________________
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]

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely need
to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to the
following?

I right-clicked my App_Data folder in the Solution Explorer and selected Add
New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked and I
was able to define several tables and relationships and also populate the
tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login failed.
Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like the
first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

 

[William \(Bill\) Vaughn]

Okay, once the database is built, you have to get rights to access it. SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll need to
depend on SSPI security. This mode assumes that the Windows User (you in the
case of a Windows Forms application) or IIS (in an ASP application) has
rights to access the SQL Server itself and the database mentioned as the
initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express) to
make sure that the IIS account for your server has rights to the database.
See the whitepapers on my blog for a few more details or Chapter 9 of my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to
the following?

I right-clicked my App_Data folder in the Solution Explorer and selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked and I
was able to define several tables and relationships and also populate the
tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like the
first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found on
my blog. I also devote an entire chapter to the subject in my book. Yes,
it can be very frustrating until you get a feel for how things work. Lots
of folks have figured it out, even old guys like me...

hth

--
____________________________________
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 going to order your book. But if I do it online, I won't have it for a
while. Can you help me find this white paper that addresses this? I browsed
one of your links but it's pretty linear and didn't see anything that looked
like what you described.

BTW, on my new Vista machine, the start menu includes:

Microsoft SQL Server 2005
I briefly ran SQL Server Configuration Manager but didn't run across
anything about access rights, and I'm unsure if that's the tool to help me
if I'm using SQL Server Express.

Thanks.

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

Okay, once the database is built, you have to get rights to access it. SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll need
to depend on SSPI security. This mode assumes that the Windows User (you
in the case of a Windows Forms application) or IIS (in an ASP application)
has rights to access the SQL Server itself and the database mentioned as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express) to
make sure that the IIS account for your server has rights to the database.
See the whitepapers on my blog for a few more details or Chapter 9 of my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to
the following?

I right-clicked my App_Data folder in the Solution Explorer and selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked and
I was able to define several tables and relationships and also populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found on
my blog. I also devote an entire chapter to the subject in my book. Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file. There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and populate
it with schema. The fact that you're going to use it in an ASP project
suggests that the SQL Server engine be located on another system in a
heavily used production application, but for most other installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand, I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and have
two seemingly unrelated ways to create my database makes it all the
more confusing.

Can anyone explain the difference between these two methods, and then
help me understand which is the best method for creating a database
to be used by my Web page?

Thanks!

 

[Jonathan Wood]

Book has been ordered, BTW.

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

Okay, once the database is built, you have to get rights to access it. SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll need
to depend on SSPI security. This mode assumes that the Windows User (you
in the case of a Windows Forms application) or IIS (in an ASP application)
has rights to access the SQL Server itself and the database mentioned as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express) to
make sure that the IIS account for your server has rights to the database.
See the whitepapers on my blog for a few more details or Chapter 9 of my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to
the following?

I right-clicked my App_Data folder in the Solution Explorer and selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked and
I was able to define several tables and relationships and also populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found on
my blog. I also devote an entire chapter to the subject in my book. Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file. There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and populate
it with schema. The fact that you're going to use it in an ASP project
suggests that the SQL Server engine be located on another system in a
heavily used production application, but for most other installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand, I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and have
two seemingly unrelated ways to create my database makes it all the
more confusing.

Can anyone explain the difference between these two methods, and then
help me understand which is the best method for creating a database
to be used by my Web page?

Thanks!

 

[William \(Bill\) Vaughn]

There are several articles up there including this one. The search engine works on this site, so just search on "Connecting".
As to the rights management, this is done through SQL Server Management studio. For the Express edition you need to download it from Microsoft from here. More instructions are here, here and here.

No, I can't say that things have gotten easier to use over the years--they haven't. It's increasingly difficult for developers to establish connections to SQL Server--especially in an ASP application.

hth


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

I'm going to order your book. But if I do it online, I won't have it for a
while. Can you help me find this white paper that addresses this? I browsed
one of your links but it's pretty linear and didn't see anything that looked
like what you described.

BTW, on my new Vista machine, the start menu includes:

Microsoft SQL Server 2005
I briefly ran SQL Server Configuration Manager but didn't run across
anything about access rights, and I'm unsure if that's the tool to help me
if I'm using SQL Server Express.

Thanks.

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

Okay, once the database is built, you have to get rights to access it. SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll need
to depend on SSPI security. This mode assumes that the Windows User (you
in the case of a Windows Forms application) or IIS (in an ASP application)
has rights to access the SQL Server itself and the database mentioned as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express) to
make sure that the IIS account for your server has rights to the database.
See the whitepapers on my blog for a few more details or Chapter 9 of my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to
the following?

I right-clicked my App_Data folder in the Solution Explorer and selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked and
I was able to define several tables and relationships and also populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found on
my blog. I also devote an entire chapter to the subject in my book. Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file. There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and populate
it with schema. The fact that you're going to use it in an ASP project
suggests that the SQL Server engine be located on another system in a
heavily used production application, but for most other installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand, I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and have
two seemingly unrelated ways to create my database makes it all the
more confusing.

Can anyone explain the difference between these two methods, and then
help me understand which is the best method for creating a database
to be used by my Web page?

Thanks!

 

[Jonathan Wood]

Thanks Bill. I'll check out all those links. However, I really feel I should
express frustration that, aside from the complexity associated with
connecting to SQL Server, it amazes me that a product like Visual Studio,
which has grown to include database tools and a small Web server engine,
requires me to download additional tools if I want my application to
actually be able to connect to the database.

Go figure. <g>

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

There are several articles up there including this one. The search engine
works on this site, so just search on "Connecting".
As to the rights management, this is done through SQL Server Management
studio. For the Express edition you need to download it from Microsoft from
here. More instructions are here, here and here.

No, I can't say that things have gotten easier to use over the years--they
haven't. It's increasingly difficult for developers to establish connections
to SQL Server--especially in an ASP application.

hth


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

I'm going to order your book. But if I do it online, I won't have it for a
while. Can you help me find this white paper that addresses this? I
browsed
one of your links but it's pretty linear and didn't see anything that
looked
like what you described.

BTW, on my new Vista machine, the start menu includes:

Microsoft SQL Server 2005
I briefly ran SQL Server Configuration Manager but didn't run across
anything about access rights, and I'm unsure if that's the tool to help me
if I'm using SQL Server Express.

Thanks.

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

Okay, once the database is built, you have to get rights to access it.
SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll need
to depend on SSPI security. This mode assumes that the Windows User (you
in the case of a Windows Forms application) or IIS (in an ASP
application)
has rights to access the SQL Server itself and the database mentioned as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express) to
make sure that the IIS account for your server has rights to the
database.
See the whitepapers on my blog for a few more details or Chapter 9 of my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to
the following?

I right-clicked my App_Data folder in the Solution Explorer and selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked and
I was able to define several tables and relationships and also populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found on
my blog. I also devote an entire chapter to the subject in my book.
Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file.
There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and
populate
it with schema. The fact that you're going to use it in an ASP
project
suggests that the SQL Server engine be located on another system in a
heavily used production application, but for most other installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand, I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and
have
two seemingly unrelated ways to create my database makes it all the
more confusing.

Can anyone explain the difference between these two methods, and
then
help me understand which is the best method for creating a database
to be used by my Web page?

Thanks!

 

[William \(Bill\) Vaughn]

Ah, I hear ya. I've been on the Visual Studio team's case for a decade (even
when I was working on the team) to get rights management tools integrated
from SSMS. To be fair, VS is a "generic" tool designed to front a variety of
backend databases, but each of the serious engines have rights management
needs as well. In the code that the drag-and-drop wizards generate, they use
the "User Instance=True" option in the Connection string that bypasses the
rights issues entirely, but (as you'll read in the book), that too has
issues of its own.
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Thanks Bill. I'll check out all those links. However, I really feel I
should express frustration that, aside from the complexity associated with
connecting to SQL Server, it amazes me that a product like Visual Studio,
which has grown to include database tools and a small Web server engine,
requires me to download additional tools if I want my application to
actually be able to connect to the database.

Go figure. <g>

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

There are several articles up there including this one. The search engine
works on this site, so just search on "Connecting".
As to the rights management, this is done through SQL Server Management
studio. For the Express edition you need to download it from Microsoft
from here. More instructions are here, here and here.

No, I can't say that things have gotten easier to use over the years--they
haven't. It's increasingly difficult for developers to establish
connections to SQL Server--especially in an ASP application.

hth


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

I'm going to order your book. But if I do it online, I won't have it for
a
while. Can you help me find this white paper that addresses this? I
browsed
one of your links but it's pretty linear and didn't see anything that
looked
like what you described.

BTW, on my new Vista machine, the start menu includes:

Microsoft SQL Server 2005

Configuration Tools
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Surface Area Configuration

I briefly ran SQL Server Configuration Manager but didn't run across
anything about access rights, and I'm unsure if that's the tool to help
me
if I'm using SQL Server Express.

Thanks.

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

Okay, once the database is built, you have to get rights to access it.
SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll
need
to depend on SSPI security. This mode assumes that the Windows User (you
in the case of a Windows Forms application) or IIS (in an ASP
application)
has rights to access the SQL Server itself and the database mentioned as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express)
to
make sure that the IIS account for your server has rights to the
database.
See the whitepapers on my blog for a few more details or Chapter 9 of my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as to
the following?

I right-clicked my App_Data folder in the Solution Explorer and
selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked
and
I was able to define several tables and relationships and also populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found
on
my blog. I also devote an entire chapter to the subject in my book.
Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I
get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The
login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file.
There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and
populate
it with schema. The fact that you're going to use it in an ASP
project
suggests that the SQL Server engine be located on another system in
a
heavily used production application, but for most other
installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand, I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and
have
two seemingly unrelated ways to create my database makes it all the
more confusing.

Can anyone explain the difference between these two methods, and
then
help me understand which is the best method for creating a database
to be used by my Web page?

Thanks!

 

[Jonathan Wood]

Hi Bill,

Ah, I hear ya. I've been on the Visual Studio team's case for a decade
(even when I was working on the team) to get rights management tools
integrated from SSMS. To be fair, VS is a "generic" tool designed to front
a variety of backend databases, but each of the serious engines have
rights management needs as well. In the code that the drag-and-drop
wizards generate, they use the "User Instance=True" option in the
Connection string that bypasses the rights issues entirely, but (as you'll
read in the book), that too has issues of its own.

MS could've at least provided some meaningful message in response to
connection errors. No database? No SQL server? Who knows? It's a freakin'
black box. One that's failed about 99% of the time for me. But let's not go
nuts--we're busy adding new features!

Anyway, I'm revisiting this now. I got your book--I seriously doubt VS2005
will be the current version by the time I get through it. But the more I
think about how connecting to a database is setup and the trouble it's been,
the more it just pisses me off.

Just had to vent. Thanks.

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

____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Thanks Bill. I'll check out all those links. However, I really feel I
should express frustration that, aside from the complexity associated
with connecting to SQL Server, it amazes me that a product like Visual
Studio, which has grown to include database tools and a small Web server
engine, requires me to download additional tools if I want my application
to actually be able to connect to the database.

Go figure. <g>

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

There are several articles up there including this one. The search engine
works on this site, so just search on "Connecting".
As to the rights management, this is done through SQL Server Management
studio. For the Express edition you need to download it from Microsoft
from here. More instructions are here, here and here.

No, I can't say that things have gotten easier to use over the
years--they haven't. It's increasingly difficult for developers to
establish connections to SQL Server--especially in an ASP application.

hth


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

I'm going to order your book. But if I do it online, I won't have it for
a
while. Can you help me find this white paper that addresses this? I
browsed
one of your links but it's pretty linear and didn't see anything that
looked
like what you described.

BTW, on my new Vista machine, the start menu includes:

Microsoft SQL Server 2005
Configuration Tools
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Surface Area Configuration

I briefly ran SQL Server Configuration Manager but didn't run across
anything about access rights, and I'm unsure if that's the tool to help
me
if I'm using SQL Server Express.

Thanks.

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

Okay, once the database is built, you have to get rights to access it.
SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll
need
to depend on SSPI security. This mode assumes that the Windows User
(you
in the case of a Windows Forms application) or IIS (in an ASP
application)
has rights to access the SQL Server itself and the database mentioned
as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express)
to
make sure that the IIS account for your server has rights to the
database.
See the whitepapers on my blog for a few more details or Chapter 9 of
my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as
to
the following?

I right-clicked my App_Data folder in the Solution Explorer and
selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked
and
I was able to define several tables and relationships and also
populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the
same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the
object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found
on
my blog. I also devote an entire chapter to the subject in my book.
Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I
get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The
login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file.
There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


message
Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and
populate
it with schema. The fact that you're going to use it in an ASP
project
suggests that the SQL Server engine be located on another system in
a
heavily used production application, but for most other
installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part
of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand,
I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and
have
two seemingly unrelated ways to create my database makes it all
the
more confusing.

Can anyone explain the difference between these two methods, and
then
help me understand which is the best method for creating a
database
to be used by my Web page?

Thanks!

 

[Jonathan Wood]

BTW, I am still completely in the dark as to if I need to download SQL
Express Edition because I don't have yet have any software that will allow a
database connection to run on my computer, or because you assumed for some
reason that I only want to use SQL Express Edition.

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

Ah, I hear ya. I've been on the Visual Studio team's case for a decade
(even when I was working on the team) to get rights management tools
integrated from SSMS. To be fair, VS is a "generic" tool designed to front
a variety of backend databases, but each of the serious engines have
rights management needs as well. In the code that the drag-and-drop
wizards generate, they use the "User Instance=True" option in the
Connection string that bypasses the rights issues entirely, but (as you'll
read in the book), that too has issues of its own.
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Thanks Bill. I'll check out all those links. However, I really feel I
should express frustration that, aside from the complexity associated
with connecting to SQL Server, it amazes me that a product like Visual
Studio, which has grown to include database tools and a small Web server
engine, requires me to download additional tools if I want my application
to actually be able to connect to the database.

Go figure. <g>

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

There are several articles up there including this one. The search engine
works on this site, so just search on "Connecting".
As to the rights management, this is done through SQL Server Management
studio. For the Express edition you need to download it from Microsoft
from here. More instructions are here, here and here.

No, I can't say that things have gotten easier to use over the
years--they haven't. It's increasingly difficult for developers to
establish connections to SQL Server--especially in an ASP application.

hth


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

I'm going to order your book. But if I do it online, I won't have it for
a
while. Can you help me find this white paper that addresses this? I
browsed
one of your links but it's pretty linear and didn't see anything that
looked
like what you described.

BTW, on my new Vista machine, the start menu includes:

Microsoft SQL Server 2005
Configuration Tools
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Surface Area Configuration

I briefly ran SQL Server Configuration Manager but didn't run across
anything about access rights, and I'm unsure if that's the tool to help
me
if I'm using SQL Server Express.

Thanks.

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

Okay, once the database is built, you have to get rights to access it.
SQL
Server has two security modes: SSPI (or Integrated) and "SQL Server"
authentication. The latter is disabled by default which means you'll
need
to depend on SSPI security. This mode assumes that the Windows User
(you
in the case of a Windows Forms application) or IIS (in an ASP
application)
has rights to access the SQL Server itself and the database mentioned
as
the initial catalog in the Connection String.
To set this up, you need to use SQL Server Management Studio (Express)
to
make sure that the IIS account for your server has rights to the
database.
See the whitepapers on my blog for a few more details or Chapter 9 of
my
book for a lot more...


--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Hi Bill,

In fact, I visited the bookstore today, but looks like I'd most likely
need to go online to find yours.

But I could sure use some help. Is there any clue you could offer as
to
the following?

I right-clicked my App_Data folder in the Solution Explorer and
selected
Add New Item.

I added an SQL Database named BlackBelt (BlackBelt.mdf). This worked
and
I was able to define several tables and relationships and also
populate
the tables.

Then I placed the following in my web.config file:

<add name="BlackBelt"
connectionString="Server=localhost\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt"/>

My code then includes the following line:

string connString =
WebConfigurationManager.ConnectionStrings["BlackBelt"].ToString();

But the following code produces an error:

using (SqlConnection conn = new SqlConnection(connString))
{
// Open database connection
conn.Open(); // ERROR HERE

The error is:

"Cannot open database "BlackBelt" requested by the login. The login
failed. Login failed for user 'Jonathan-PC\Jonathan'."

I do not get the SSPI part and if I change that to True, I get the
same
exact same error except that it does not show it to me in the IDE like
the first syntax did.

I would be extremely grateful for even the slightest clue.

Thanks.

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

The DBO you're seeing in the server explorer is the database "owner"
designation. This permits the server to tie the identity of the
object
(the database in this case) to a specific user/login account.
I've written a number of whitepapers on connecting that can be found
on
my blog. I also devote an entire chapter to the subject in my book.
Yes,
it can be very frustrating until you get a feel for how things work.
Lots of folks have figured it out, even old guys like me...

hth

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

Bill,

I don't know why this is so infuriatingly difficult. I would say I
get
a successful database connection maybe one out of every hundred
attempts. And the messages are virtually useless.

Here's the current:

{"Cannot open database \"BlackBelt\" requested by the login. The
login
failed.\r\nLogin failed for user 'Jonathan-PC\\Jonathan'."}

I don't know how much hair I can withstand pulling out before this
starts making a bit of sense to me.

Thanks.

(Also, I noticed that, when I create a database via the Server
Explorer, it appears to create a DBO file instead of an MDF file.
There
seems to be other differences as well. I've been programming for a
hundred years (almost) but feel completely useless right now.)

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


message
Either method is acceptable. They both create a database .mdf file
that can be populated with appropriate schema. As a matter of fact,
there are probably a half-dozen ways to create a database and
populate
it with schema. The fact that you're going to use it in an ASP
project
suggests that the SQL Server engine be located on another system in
a
heavily used production application, but for most other
installations
it does not make much difference.

I discuss how to decide in depth in my book.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

It appears there are two methods for creating a database as part
of
an ASP project.

I can use the Server Explorer and create a database that I can't
easily see as a file, and create a connection. On the other hand,
I
can right click in the Solution Explorer window and select Add New
Item, and then create an SQL Database. This creates an MDF file
directly.

I'm having trouble getting up to speed on the database stuff and
have
two seemingly unrelated ways to create my database makes it all
the
more confusing.

Can anyone explain the difference between these two methods, and
then
help me understand which is the best method for creating a
database
to be used by my Web page?

Thanks!