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?
>