Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
--Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.
> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.
> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.
> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.
> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.
> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.
> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?
> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
--|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.
> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
--
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment