Access a database from a client application

Hi all...

I am writing a Windows Application which will be used by about 100 clients. (NT Authenticated)

The application is going to be used in a LAN enviroment. User who have access will download a VB.NET application from a web site.

Of course, I want to prevent any of the 100 users from using a tool such as Query Analysis or ODBC to connect directly to the database and modify data.

So, it appears the way to go is to the "Application Roles". For this to work, the application roll password has to be saved in the application to execute the sp_setapprole procedure.

Is there a way to secure this password? I am worried about a user somehow extracting the password from the application and connecting directly.

Am I on the right track here? Any ideas appreciated!

Thanks,

Forch

One method I have used is to create an admin system where you would create a Hash of the application name and version + a secret string.... Then use this hash as the password for the Application Role. Doing it this way will also make sure that the users will use the current version only..

|||

Note that if the user has full control over your application and can debug it, he can break any protection scheme that you build into it. It's worth obfuscating the password, as Glenn suggested, but this protection can be broken by a knowledgeable user.

Thanks
Laurentiu

access 2003 to SQL server migration

We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures
1. You can make them a member of the datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production server anyway.
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrote in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures
|||makes sense on #2 i jsut wanted to rule it out because i'm sure its going to get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:%23JvZHzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production server anyway.
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrote in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures

access 2003 to SQL server migration

We are migrating one of our clients databases from an access 2003 database t
o their sql server 2000 database being that i have mostly worked with oracle
datbases I had come up with a list of things that we were going to have to
implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not ins
ert update or delete from any exisitng table and any new tables without havi
ng to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have
them promoted to DBO or can you only do that with stored procedures1. You can make them a member of the datareader role and then they have acc
ess to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production
server anyway.
--
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrot
e in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database t
o their sql server 2000 database being that i have mostly worked with oracle
datbases I had come up with a list of things that we were going to have to
implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not ins
ert update or delete from any exisitng table and any new tables without havi
ng to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have
them promoted to DBO or can you only do that with stored procedures|||makes sense on #2 i jsut wanted to rule it out because i'm sure its going to
get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:%23JvZ
HzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they have acc
ess to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production
server anyway.
--
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrot
e in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database t
o their sql server 2000 database being that i have mostly worked with oracle
datbases I had come up with a list of things that we were going to have to
implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not ins
ert update or delete from any exisitng table and any new tables without havi
ng to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have
them promoted to DBO or can you only do that with stored procedures

Accesing SQL Server with clients

i have a MS Windows 2003 Enterprise Edition but i don't have PDC installed so i don't have a domain in the LAN, i installed SQL Server 2000 on this server, but the clients ( MSDE 2000 ) can not se the SQL server.
My cuestion is , i have to install a pdc and then mount SQL server , and add the clients to the domain
"Jaxon" wrote:

> I am sure there are tons of white papers on this, but I dont suspect that
> you'll experience any huge hurdles.
> MSDE is essentially a throttled down version of the SQL Server Engine.
> "Because it is fully compatible with other editions of SQL Server,
> developers can easily target both SQL Server and MSDE 2000 with the same
> core code base. This provides a seamless upgrade path from MSDE 2000 to SQL
> Server if an application grows beyond the storage and scalability limits of
> MSDE 2000."
>
> http://www.microsoft.com/sql/msde/pr...o/overview.asp
>
>
> Greg Jackson
> PDX, Oregon
>
>
MSDE doesn't have network access enabled by default. Enable using svrnetcn.exe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Visual FoxPro programmer" <VisualFoxProprogrammer@.discussions.microsoft.com> wrote in message
news:927CFF5D-751E-4040-AF0D-B3896E64D68C@.microsoft.com...
> i have a MS Windows 2003 Enterprise Edition but i don't have PDC installed so i don't have a
domain in the LAN, i installed SQL Server 2000 on this server, but the clients ( MSDE 2000 ) can not
se the SQL server.
> My cuestion is , i have to install a pdc and then mount SQL server , and add the clients to the
domain[vbcol=seagreen]
>
> "Jaxon" wrote:

Accesing SQL Server with clients

i have a MS Windows 2003 Enterprise Edition but i don't have PDC installed s
o i don't have a domain in the LAN, i installed SQL Server 2000 on this serv
er, but the clients ( MSDE 2000 ) can not se the SQL server.
My cuestion is , i have to install a pdc and then mount SQL server , and add
the clients to the domain
"Jaxon" wrote:

> I am sure there are tons of white papers on this, but I dont suspect that
> you'll experience any huge hurdles.
> MSDE is essentially a throttled down version of the SQL Server Engine.
> "Because it is fully compatible with other editions of SQL Server,
> developers can easily target both SQL Server and MSDE 2000 with the same
> core code base. This provides a seamless upgrade path from MSDE 2000 to SQ
L
> Server if an application grows beyond the storage and scalability limits o
f
> MSDE 2000."
>
> http://www.microsoft.com/sql/msde/p...fo/overview.asp
>
>
> Greg Jackson
> PDX, Oregon
>
>MSDE doesn't have network access enabled by default. Enable using svrnetcn.e
xe.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Visual FoxPro programmer" <VisualFoxProprogrammer@.discussions.microsoft.com
> wrote in message
news:927CFF5D-751E-4040-AF0D-B3896E64D68C@.microsoft.com...
> i have a MS Windows 2003 Enterprise Edition but i don't have PDC installed so i do
n't have a
domain in the LAN, i installed SQL Server 2000 on this server, but the clien
ts ( MSDE 2000 ) can not
se the SQL server.
> My cuestion is , i have to install a pdc and then mount SQL server , and add the c
lients to the
domain[vbcol=seagreen]
>
> "Jaxon" wrote:
>

Acces Enterprise console

Hi everyone,
Can someone tell me if there are any tools to access the enterprise console
of sql server from another pc?
I've got 4 w2k clients and 1 w2k server.
Who can help me?
--
Pascual de la Cruz
Data Manager
Generation R
Erasmus MC
P.O. Box 1738
3000 DR Rotterdam
The Netherlands
Phone: +31 (0)10-4087479
Mail: delacruz@.generationr.nlYou simply need to install SQL Server client tools on your other PC. If you
cannot fo that, then log onto your SQL Server using Remote Desktop
connection.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Nicollo Machiaveli" <machiaveli280278@.hotmail.com> wrote in message
news:udIw5qHaDHA.4028@.tk2msftngp13.phx.gbl...
Hi everyone,
Can someone tell me if there are any tools to access the enterprise console
of sql server from another pc?
I've got 4 w2k clients and 1 w2k server.
Who can help me?
--
Pascual de la Cruz
Data Manager
Generation R
Erasmus MC
P.O. Box 1738
3000 DR Rotterdam
The Netherlands
Phone: +31 (0)10-4087479
Mail: delacruz@.generationr.nl|||Yes. Run thesetup program, and you can select Client Components (or whathever the selection is
named).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Nicollo Machiaveli" <machiaveli280278@.hotmail.com> wrote in message
news:uz6UsELaDHA.384@.TK2MSFTNGP12.phx.gbl...
> Where can i find the client tools? Are they on the cd of sql server
> enterprise?
> --
> Pascual de la Cruz
> Data Manager
> Generation R
> Erasmus MC
> P.O. Box 1738
> 3000 DR Rotterdam
> The Netherlands
> Phone: +31 (0)10-4087479
> Mail: delacruz@.generationr.nl
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:eC0Z9gKaDHA.2648@.TK2MSFTNGP09.phx.gbl...
> > You simply need to install SQL Server client tools on your other PC. If
> you
> > cannot fo that, then log onto your SQL Server using Remote Desktop
> > connection.
> >
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > What hardware is your SQL Server running on?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> > "Nicollo Machiaveli" <machiaveli280278@.hotmail.com> wrote in message
> > news:udIw5qHaDHA.4028@.tk2msftngp13.phx.gbl...
> > Hi everyone,
> >
> > Can someone tell me if there are any tools to access the enterprise
> console
> > of sql server from another pc?
> >
> > I've got 4 w2k clients and 1 w2k server.
> >
> > Who can help me?
> >
> > --
> > Pascual de la Cruz
> > Data Manager
> > Generation R
> > Erasmus MC
> > P.O. Box 1738
> > 3000 DR Rotterdam
> > The Netherlands
> > Phone: +31 (0)10-4087479
> > Mail: delacruz@.generationr.nl
> >
> >
> >
>

About the sql 2005 client software

Hello,

Assume that I have a ms sql 2005 server running,

and the clients wants to connect to the database through their Windows XP desktops,

is there any sql 2005 client software that can install on the clients desktop for them to use? (e.g. like MySQLFront or PLSQL)

so that they can input sql statement or store procedure on the client side to make query to the database.

Does any things should notice during installation?

Thanks for helping!!

Ah, if you write a program (or use a program) that can create an OLE DB or .NET SqlClient connection, you can connect to SQL Server 2005--assuming that it's enabled to see the LAN (it's disabled by default). Even VB6 or older programs can use the SQL Native Client with an OLE DB-enabled application to connect to SQL Server 2005.

hth

|||

Hi William,

Do you know is this possible for using a free Microsoft SQL Server 2005 Express Edition as a client side interface, intall it on Windows XP. Use it to connect to a sql 2005 standard server to make query and update information on table or views, by using the user's sql login name & password of the sql 2005 standard server to authenticate.

Or I must use win2003 remote desktop with terminal server client license to let the users to remote to the sql 2005 standard server directly & logon locally to achieve this goal.

Thanks!

|||Again, this is a very fundemental question that can best be addressed by some research. No, you don't need SQL Server Express edition installed on the client systems if you intend to write (or use) and application that connects to a remote server. Try picking up a copy of my Hitchhiker's Guide to Visual Basic and SQL Server 6th Edition that discusses these basic concepts.

About RowLock

How i have to lock the row while updation of the row while that row is
accessed by various clients and also how i have to release the lock
Hello -
I would like to provide you couple of reference, hope that helps you.
1. Using Hints in Query
-- This Statement will not produce any locks to the Author Table in Pubs
Database.
Select * from pubs.dbo.authors with (NOLOCK)
Similarly you can use with (ROWLOCK),with (TABLOCK),with(PAGLOCK) in Insert
or Update Statements.
Please check different Isolation Level Mechanism incorporated in SQL Server
Using SP_INDEXOPTION
You can override how SQL Server performs locking on a table by using the
SP_INDEXOPTION command. Below is an example of code you can run to tell SQL
Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
Check SP_INDEXOPTION for details in Books On Line in SQL Server.
Thanks
Surajit
"balakarthik" wrote:

> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock
|||Actually I think the first answer is that you do not have to do anything...
When you update a row, SQL will automatically acquire the locks to protect
the row.
If you are changing a row in a user defined transaction ( Begin tran -
commit) SQL will hold the lock for the duration of the transaction... If you
wish to control locking yourself for some reason, the hints etc is the way..
most of the time I do not use hints - use them only if the standard locking
scheme does not give you the protection you need.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:458E7B1F-D387-4771-B844-02B4B8EDC4E0@.microsoft.com...
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock

About RowLock

How i have to lock the row while updation of the row while that row is
accessed by various clients and also how i have to release the lockHello -
I would like to provide you couple of reference, hope that helps you.
1. Using Hints in Query
-- This Statement will not produce any locks to the Author Table in Pubs
Database.
Select * from pubs.dbo.authors with (NOLOCK)
Similarly you can use with (ROWLOCK),with (TABLOCK),with(PAGLOCK) in Insert
or Update Statements.
Please check different Isolation Level Mechanism incorporated in SQL Server
Using SP_INDEXOPTION
You can override how SQL Server performs locking on a table by using the
SP_INDEXOPTION command. Below is an example of code you can run to tell SQL
Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
Check SP_INDEXOPTION for details in Books On Line in SQL Server.
Thanks
Surajit
"balakarthik" wrote:
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock|||Actually I think the first answer is that you do not have to do anything...
When you update a row, SQL will automatically acquire the locks to protect
the row.
If you are changing a row in a user defined transaction ( Begin tran -
commit) SQL will hold the lock for the duration of the transaction... If you
wish to control locking yourself for some reason, the hints etc is the way..
most of the time I do not use hints - use them only if the standard locking
scheme does not give you the protection you need.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:458E7B1F-D387-4771-B844-02B4B8EDC4E0@.microsoft.com...
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock

About RowLock

How i have to lock the row while updation of the row while that row is
accessed by various clients and also how i have to release the lockHello -
I would like to provide you couple of reference, hope that helps you.
1. Using Hints in Query
-- This Statement will not produce any locks to the Author Table in Pubs
Database.
Select * from pubs.dbo.authors with (NOLOCK)
Similarly you can use with (ROWLOCK),with (TABLOCK),with(PAGLOCK) in Insert
or Update Statements.
Please check different Isolation Level Mechanism incorporated in SQL Server
Using SP_INDEXOPTION
You can override how SQL Server performs locking on a table by using the
SP_INDEXOPTION command. Below is an example of code you can run to tell SQL
Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
Check SP_INDEXOPTION for details in Books On Line in SQL Server.
Thanks
Surajit
"balakarthik" wrote:

> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock|||Actually I think the first answer is that you do not have to do anything...
When you update a row, SQL will automatically acquire the locks to protect
the row.
If you are changing a row in a user defined transaction ( Begin tran -
commit) SQL will hold the lock for the duration of the transaction... If you
wish to control locking yourself for some reason, the hints etc is the way..
most of the time I do not use hints - use them only if the standard locking
scheme does not give you the protection you need.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:458E7B1F-D387-4771-B844-02B4B8EDC4E0@.microsoft.com...
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock

about multiple clients

I have a sql server application program developped with ODBC. The program can
run at different computer and realize synchronization operation. But
sometimes it has error message :
"Attemp to scroll past end or before beginning of data".
Could you tell me the reason ?
it seems the program got failure when trying to find next row, but the
position may not point to an valid row,since the row could be deleted by
other process from another machine.

about multiple clients

I have a sql server application program developped with ODBC. The program ca
n
run at different computer and realize synchronization operation. But
sometimes it has error message :
"Attemp to scroll past end or before beginning of data".
Could you tell me the reason ?it seems the program got failure when trying to find next row, but the
position may not point to an valid row,since the row could be deleted by
other process from another machine.

About automatic process at midnight

Hi
I have a table1 which hold a field (amount) with 1000 rows
The amount in the 1000 rows is consumed by clients during the day
The next day the amounts have to return to their original values (at this
time I’m doing that by hand)
Now I’m going to save the original values in another table
My question is: Can the SQL server copy the original values from table1 to
table2 at midnight or at a time I schedule?
Thks again.Yes. Create a SQL Agent job. Make sure the SQLSERVERAGENT service is set t
o
Automatic.
"Kenny M." wrote:

> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I’m doing that by hand)
> Now I’m going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>|||Insert them into a new table
1.INSERT INTO NewTableName
Select * From OldTableName
2.Update the Working table (at night after the work) with a update statement
referencing the ID column of the table
BEGIN Transaction
DELETE FROM Oldtable where ID IN (Select IDCOLUMN From NewTable)
INSERT INTO Oldtable SELECT * from NewTable
IF @.@.ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it that what you ment ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kenny M." <KennyM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D19D255D-E863-4E71-8E53-2145187103A7@.microsoft.com...
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I'm doing that by hand)
> Now I'm going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>