how to connect with sqlexpress2005 & asp.net for update insert delete codings

I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters

import System.Data.SqlClient;

related update delete insert codings ...

How to know about the make the new connection with different databases sqlserver, oracle mysql...

Hi

Have you had a lookhttp://quickstarts.asp.net/QuickStartv20/default.aspx andhttp://www.asp.net/learn/data-access/.

Work through these samples.. They explain how to access databases using declarative coding (sqldatasource and the likes) and coe behind coding.

Hope that helps.

VJ

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB2005 a

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

sql

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB20

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

How to configure SQLExpress on a workgroup

I recently deployed an app with SQLExpress2005 as the backend. Despite
testing it here on my dev network, this was my first opportunity to use it
in a workgroup environment. I had to really jump through some hoops to get
connectivity between the app and the SQLExpress database. I'm asking for any
advice or constructive criticism on the steps I took, particularly in
regards to whether it was unnecessary or exposed any security issues.
Scenario:
-- Four PCs (all XP Pro) in a workgroup configuration -- no server,
connectivity is through a router.
-- SQLExpress installed on one of the PCs with a server instance
"PC1\SQLExpress".
-- Database "AppDB" attached to "PC1\SQLExpress".
-- A .Net application is installed on all workgroup PCs. The app works "as
advertised" on the PC where there SQLExpress is installed, with connection
string security set to SSPI.
Could not get connection between the apps on the other PCs to "AppDB",
sooooo ...
On the SQLExpress PC:
1. Ensured firewall had ports 1433 and 1434 exposed.
2. Added ALL the local workgroup users and their passwords to the XP machine
(PC1).
3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
over TCP/IP only.
4. Added "PC1\GUEST to SQLExpress Logins.
On the downrange PCs, the app now reported, "Cannot open database "AppDB"
requested by login."
5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ ->
Properties -> UserMapping and checked Map "AppDB"
On the downrange PCs, the app now reported, "EXECUTE permission denied on
"sel_Employees". So it appears that the user mapping WAS required.
6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
Database Role Membership and set to db_owner.
The application on all downrange PCs now connect and function as advertised!
This sure strikes me as incredibly Rube Goldberg in order to get connection
between local network apps and an SQLExpress database. It seems like a DBA
would be required even for SQLExpress installs, which surely is not what
Microsoft intended. What could I have done better or easier?How about having your app using a SQL Server login, perhaps with a password not known to the users
of the other machines? that would cut off a lot of the steps you took.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <brikshoe@.newsgroups.nospam> wrote in message news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite testing it here on my dev
>network, this was my first opportunity to use it in a workgroup environment. I had to really jump
>through some hoops to get connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in regards to whether it was
>unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server, connectivity is through a
> router.
> -- SQLExpress installed on one of the PCs with a server instance "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as advertised" on the PC
> where there SQLExpress is installed, with connection string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB", sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB" requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ -> Properties ->
> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on "sel_Employees". So it
> appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties -> Database Role Membership
> and set to db_owner.
> The application on all downrange PCs now connect and function as advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get connection between local network
> apps and an SQLExpress database. It seems like a DBA would be required even for SQLExpress
> installs, which surely is not what Microsoft intended. What could I have done better or easier?
>|||"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
Using Express and using Workgroup Authentication each introduce one extra
step. Express requires remote connections to be enabled manually and
Workgroup Authentication requires the creation of the local accounts and the
manual synchronization of passwords.
Steps 4,5 and 6 are always required in SQL Server. You must add a login for
the users, grant database access for the login, and assign database role
memberships.
David|||I don't think you want to enable the Guest account as a default for all
users. There's no security in that.
I would create a Security Group on the PC1 SQL host computer, something like
AppDBUsers. Then add each of the user accounts that you want to have access
to the database to that group. If different users have different db
privileges create a separate security group for each privilege set or job
function or however you want to distinguish. Then add the individual user
accounts to the appropriate security groups.
In SQL Server, create a login for each security group(s) and give that login
access to the db. Don't make that login the db owner. Instead you have the
opportunity to specify exactly what permissions you want each login to have
on each db object. Or if you don't need that level of granularity, you can
make that login a db reader and a db writer, so they can read and write
everything. If you have stored procedures you need to set permissions for
those too.
Even if you only have a few users now, the indirection of using the security
groups makes it easier to know who has which permissions. And if you get a
new user, all you have to do is add that one user to the appropriate
group(s) and they will have the appropriate db access.
Paul Shapiro
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>|||Thanks to Tibor, David, and Paul. This certainly cleared up my
(mis)understanding of the issue.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>

How to configure SQLExpress on a workgroup

I recently deployed an app with SQLExpress2005 as the backend. Despite
testing it here on my dev network, this was my first opportunity to use it
in a workgroup environment. I had to really jump through some hoops to get
connectivity between the app and the SQLExpress database. I'm asking for any
advice or constructive criticism on the steps I took, particularly in
regards to whether it was unnecessary or exposed any security issues.
Scenario:
-- Four PCs (all XP Pro) in a workgroup configuration -- no server,
connectivity is through a router.
-- SQLExpress installed on one of the PCs with a server instance
"PC1\SQLExpress".
-- Database "AppDB" attached to "PC1\SQLExpress".
-- A .Net application is installed on all workgroup PCs. The app works "as
advertised" on the PC where there SQLExpress is installed, with connection
string security set to SSPI.
Could not get connection between the apps on the other PCs to "AppDB",
sooooo ...
On the SQLExpress PC:
1. Ensured firewall had ports 1433 and 1434 exposed.
2. Added ALL the local workgroup users and their passwords to the XP machine
(PC1).
3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
over TCP/IP only.
4. Added "PC1\GUEST to SQLExpress Logins.
On the downrange PCs, the app now reported, "Cannot open database "AppDB"
requested by login."
5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ ->
Properties -> UserMapping and checked Map "AppDB"
On the downrange PCs, the app now reported, "EXECUTE permission denied on
"sel_Employees". So it appears that the user mapping WAS required.
6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
Database Role Membership and set to db_owner.
The application on all downrange PCs now connect and function as advertised!
This sure strikes me as incredibly Rube Goldberg in order to get connection
between local network apps and an SQLExpress database. It seems like a DBA
would be required even for SQLExpress installs, which surely is not what
Microsoft intended. What could I have done better or easier?
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
Using Express and using Workgroup Authentication each introduce one extra
step. Express requires remote connections to be enabled manually and
Workgroup Authentication requires the creation of the local accounts and the
manual synchronization of passwords.
Steps 4,5 and 6 are always required in SQL Server. You must add a login for
the users, grant database access for the login, and assign database role
memberships.
David
|||I don't think you want to enable the Guest account as a default for all
users. There's no security in that.
I would create a Security Group on the PC1 SQL host computer, something like
AppDBUsers. Then add each of the user accounts that you want to have access
to the database to that group. If different users have different db
privileges create a separate security group for each privilege set or job
function or however you want to distinguish. Then add the individual user
accounts to the appropriate security groups.
In SQL Server, create a login for each security group(s) and give that login
access to the db. Don't make that login the db owner. Instead you have the
opportunity to specify exactly what permissions you want each login to have
on each db object. Or if you don't need that level of granularity, you can
make that login a db reader and a db writer, so they can read and write
everything. If you have stored procedures you need to set permissions for
those too.
Even if you only have a few users now, the indirection of using the security
groups makes it easier to know who has which permissions. And if you get a
new user, all you have to do is add that one user to the appropriate
group(s) and they will have the appropriate db access.
Paul Shapiro
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
|||Thanks to Tibor, David, and Paul. This certainly cleared up my
(mis)understanding of the issue.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>

How to configure SQLExpress on a workgroup

I recently deployed an app with SQLExpress2005 as the backend. Despite
testing it here on my dev network, this was my first opportunity to use it
in a workgroup environment. I had to really jump through some hoops to get
connectivity between the app and the SQLExpress database. I'm asking for any
advice or constructive criticism on the steps I took, particularly in
regards to whether it was unnecessary or exposed any security issues.
Scenario:
-- Four PCs (all XP Pro) in a workgroup configuration -- no server,
connectivity is through a router.
-- SQLExpress installed on one of the PCs with a server instance
"PC1\SQLExpress".
-- Database "AppDB" attached to "PC1\SQLExpress".
-- A .Net application is installed on all workgroup PCs. The app works "as
advertised" on the PC where there SQLExpress is installed, with connection
string security set to SSPI.
Could not get connection between the apps on the other PCs to "AppDB",
sooooo ...
On the SQLExpress PC:
1. Ensured firewall had ports 1433 and 1434 exposed.
2. Added ALL the local workgroup users and their passwords to the XP machine
(PC1).
3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
over TCP/IP only.
4. Added "PC1\GUEST to SQLExpress Logins.
On the downrange PCs, the app now reported, "Cannot open database "AppDB"
requested by login."
5. On "PC1\SQLExpress instance, went to Security -> Logins -> PC1\Guest\ ->
Properties -> UserMapping and checked Map "AppDB"
On the downrange PCs, the app now reported, "EXECUTE permission denied on
"sel_Employees". So it appears that the user mapping WAS required.
6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
Database Role Membership and set to db_owner.
The application on all downrange PCs now connect and function as advertised!
This sure strikes me as incredibly Rube Goldberg in order to get connection
between local network apps and an SQLExpress database. It seems like a DBA
would be required even for SQLExpress installs, which surely is not what
Microsoft intended. What could I have done better or easier?
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
Using Express and using Workgroup Authentication each introduce one extra
step. Express requires remote connections to be enabled manually and
Workgroup Authentication requires the creation of the local accounts and the
manual synchronization of passwords.
Steps 4,5 and 6 are always required in SQL Server. You must add a login for
the users, grant database access for the login, and assign database role
memberships.
David
|||I don't think you want to enable the Guest account as a default for all
users. There's no security in that.
I would create a Security Group on the PC1 SQL host computer, something like
AppDBUsers. Then add each of the user accounts that you want to have access
to the database to that group. If different users have different db
privileges create a separate security group for each privilege set or job
function or however you want to distinguish. Then add the individual user
accounts to the appropriate security groups.
In SQL Server, create a login for each security group(s) and give that login
access to the db. Don't make that login the db owner. Instead you have the
opportunity to specify exactly what permissions you want each login to have
on each db object. Or if you don't need that level of granularity, you can
make that login a db reader and a db writer, so they can read and write
everything. If you have stored procedures you need to set permissions for
those too.
Even if you only have a few users now, the indirection of using the security
groups makes it easier to know who has which permissions. And if you get a
new user, all you have to do is add that one user to the appropriate
group(s) and they will have the appropriate db access.
Paul Shapiro
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>
|||Thanks to Tibor, David, and Paul. This certainly cleared up my
(mis)understanding of the issue.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eB585wxZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>I recently deployed an app with SQLExpress2005 as the backend. Despite
>testing it here on my dev network, this was my first opportunity to use it
>in a workgroup environment. I had to really jump through some hoops to get
>connectivity between the app and the SQLExpress database. I'm asking for
>any advice or constructive criticism on the steps I took, particularly in
>regards to whether it was unnecessary or exposed any security issues.
> Scenario:
> -- Four PCs (all XP Pro) in a workgroup configuration -- no server,
> connectivity is through a router.
> -- SQLExpress installed on one of the PCs with a server instance
> "PC1\SQLExpress".
> -- Database "AppDB" attached to "PC1\SQLExpress".
> -- A .Net application is installed on all workgroup PCs. The app works "as
> advertised" on the PC where there SQLExpress is installed, with connection
> string security set to SSPI.
> Could not get connection between the apps on the other PCs to "AppDB",
> sooooo ...
> On the SQLExpress PC:
> 1. Ensured firewall had ports 1433 and 1434 exposed.
> 2. Added ALL the local workgroup users and their passwords to the XP
> machine (PC1).
> 3. Used the SQL Surface Area Config tool. Set to allow Remote Connections
> over TCP/IP only.
> 4. Added "PC1\GUEST to SQLExpress Logins.
> On the downrange PCs, the app now reported, "Cannot open database "AppDB"
> requested by login."
> 5. On "PC1\SQLExpress instance, went to Security -> Logins ->
> PC1\Guest\ -> Properties -> UserMapping and checked Map "AppDB"
> On the downrange PCs, the app now reported, "EXECUTE permission denied on
> "sel_Employees". So it appears that the user mapping WAS required.
> 6. On "AppDB", went to Security -> Users -> PC1\Guest -> Properties ->
> Database Role Membership and set to db_owner.
> The application on all downrange PCs now connect and function as
> advertised!
> This sure strikes me as incredibly Rube Goldberg in order to get
> connection between local network apps and an SQLExpress database. It seems
> like a DBA would be required even for SQLExpress installs, which surely is
> not what Microsoft intended. What could I have done better or easier?
>