This does not seem like rocket science but I can't find a recommendation
from MS on which uplevel (from defaults) Security Template (file server,
infrastructure server, etc.) is the best baseline starting point for a SQL
install on a W2K3 AD. I realize that mods will be necessary no matter but
since there are 700+ settings having a head start seems like a good thing. I
have created the OU specifically for the SQL server and just need to
configure it's GP including IPSec. I have implemented most of the suggested
Security settings to the install of SQL itself (see prior post).
Any suggestions?
ThanksThe Enterprise - Member Server Template is the template that should be
used. Also, generate a rollback template to help you with your testing.
The High Security Template should not be used.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Showing posts with label security. Show all posts
Showing posts with label security. Show all posts
Tuesday, March 27, 2012
Best security structure for meeting internal IT controls requirements? (Sarbanes-Oxley
I'm trying to determine the best way to set up SQL Server 2000 security to m
eet our requirements for strong internal IT controls. Due to Sarbanes-Oxley
rules, we have to be able to show auditors that either IT is prevented from
causing a data issue or ca
n be detected if they can't be prevented.
The specific concern is with the DBA account and it's ability within the Sys
tem Administrator role to do essentially anything, including perhaps writing
unauthorized SQL statements that could insert/update/delete financial data
in the production database.
I've been experimenting with some approaches but would like to know what Mic
rosoft is recommending for meeting these requirements. I'm also interested
in anyone else's take on it as well.
So far I've considered:
1) Remove DBA from SA role and add to other fixed server roles and fixed dat
abase roles (except db_owner). From what I've tested, it seems that the DBA
could still perform all functions except adding/removing users from the rol
es and if put in the db_den
ydatawriter role, cannot modify the data or remove the role. This means tha
t user security would have to be handled by someone else, which is not exact
ly ideal, since to my mind, it's just shifting the risk as all of our IT peo
ple know SQL to some extent
and there is no graceful way for a non IT person to manage it. We could add
a separate security login with a dual-keyed password, so that it would take
2 people, perhaps one IT and one non to administer security...
2) Remove DBA from SA role and add to other fixed server roles and fixed dat
abase roles (including db_owner). The DBA cannot add/remove users from the
fixed server roles, which should be fine, because there would not normally b
e any need for someone to b
e in them. The DBA could remove herself from the db_denydatawriter role, bu
t if we turn on tracing and monitor for that event, an internal controls rev
iewer could catch it, which is much easier than trying to monitor for transa
ctions all the time.
3) Leave DBA as-is in SA role and use 3rd party monitoring tool to track DBA
activity. So far, I've been looking into Entegra, but the problem is that
it doesn't provide transaction monitoring per se, although you can query the
data it collects for trans
actions. Does anyone know of a 3rd party tool that would do it all?
What other models are out there? What would you recommend? Thanks in advan
ce-
Shelley M. Maas
Development Manager
Apio, Inc.Hi Shelley,
From your descriptions, I understood that you would like to select a best
practice for your System Security. If there is anything I misunderstood,
please feel free to let me know
First of all, I think the most suitable one will be the best practice for
your system and I could not figure out which one will be the suitable out
of three. I think the following documents will be helpful to you in
decision:
Checklist: Securing Your Database Server
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/CL_SecDBSe.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.c...ql_security.asp
However, since this is a consultation type issue, you can contact Advisory
Services (AS) . Microsoft Advisory Services provides short-term advice and
guidance for problems not covered by Problem Resolution Service as well as
requests for consultative assistance for design, development and deployment
issues. You may call this number to get Advisory Services: (800) 936-5200.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
****************************************
*******************
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.|||Michael,
Thanks for your reply. I've read both of the articles in the past, and whil
e they offer a lot of helpful information, they do not address my concern, w
hich is more related to internal controls and permissions for IT than to ext
ernal threats or internal n
on IT threats. I assume that AS is a cost-based service, so I wanted to see
if I could get some general suggestions and comments before heading down th
at road. I have to believe that others are dealing with this issue and may
have already found good ans
wers.|||Hi Shelley,
Thanks for your updating
Based on my knowledge, I think the first solution of three is better than
others. However, I am not sure whether it will be the best practice for
your senario, as I have said, I could not define the best practice through
newsgroup. The reason why first one is better than okther two is that the
first one could prevent what might happened while other two just monitor
what happened. When there is a damage on database that could not be
recovered, last two is useless, while I think the first one could prevent
it from happening.
Seocndly, SQL Server has such functionality to track DBA activieties. You'd
better have a look at Books Online or MSDN Online with topic "C2 Auditing".
C2 auditing, which requires that you follow very specific security
policies. You could follow this to make it effective (You need restart your
server). Attention please, opening c2 auditing will have an IMPACT on your
server performance!
-In Query Analyzer
sp_configure 'show',1
go
reconfigure with override
go
sp_configure 'c2',1
reconfigure with override
go
--then restart the server, you will see an trc file on your data directory
now. AGAIN, this kind of writing into disk will have an influence on your
performance
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
eet our requirements for strong internal IT controls. Due to Sarbanes-Oxley
rules, we have to be able to show auditors that either IT is prevented from
causing a data issue or ca
n be detected if they can't be prevented.
The specific concern is with the DBA account and it's ability within the Sys
tem Administrator role to do essentially anything, including perhaps writing
unauthorized SQL statements that could insert/update/delete financial data
in the production database.
I've been experimenting with some approaches but would like to know what Mic
rosoft is recommending for meeting these requirements. I'm also interested
in anyone else's take on it as well.
So far I've considered:
1) Remove DBA from SA role and add to other fixed server roles and fixed dat
abase roles (except db_owner). From what I've tested, it seems that the DBA
could still perform all functions except adding/removing users from the rol
es and if put in the db_den
ydatawriter role, cannot modify the data or remove the role. This means tha
t user security would have to be handled by someone else, which is not exact
ly ideal, since to my mind, it's just shifting the risk as all of our IT peo
ple know SQL to some extent
and there is no graceful way for a non IT person to manage it. We could add
a separate security login with a dual-keyed password, so that it would take
2 people, perhaps one IT and one non to administer security...
2) Remove DBA from SA role and add to other fixed server roles and fixed dat
abase roles (including db_owner). The DBA cannot add/remove users from the
fixed server roles, which should be fine, because there would not normally b
e any need for someone to b
e in them. The DBA could remove herself from the db_denydatawriter role, bu
t if we turn on tracing and monitor for that event, an internal controls rev
iewer could catch it, which is much easier than trying to monitor for transa
ctions all the time.
3) Leave DBA as-is in SA role and use 3rd party monitoring tool to track DBA
activity. So far, I've been looking into Entegra, but the problem is that
it doesn't provide transaction monitoring per se, although you can query the
data it collects for trans
actions. Does anyone know of a 3rd party tool that would do it all?
What other models are out there? What would you recommend? Thanks in advan
ce-
Shelley M. Maas
Development Manager
Apio, Inc.Hi Shelley,
From your descriptions, I understood that you would like to select a best
practice for your System Security. If there is anything I misunderstood,
please feel free to let me know
First of all, I think the most suitable one will be the best practice for
your system and I could not figure out which one will be the suitable out
of three. I think the following documents will be helpful to you in
decision:
Checklist: Securing Your Database Server
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/CL_SecDBSe.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.c...ql_security.asp
However, since this is a consultation type issue, you can contact Advisory
Services (AS) . Microsoft Advisory Services provides short-term advice and
guidance for problems not covered by Problem Resolution Service as well as
requests for consultative assistance for design, development and deployment
issues. You may call this number to get Advisory Services: (800) 936-5200.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
****************************************
*******************
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.|||Michael,
Thanks for your reply. I've read both of the articles in the past, and whil
e they offer a lot of helpful information, they do not address my concern, w
hich is more related to internal controls and permissions for IT than to ext
ernal threats or internal n
on IT threats. I assume that AS is a cost-based service, so I wanted to see
if I could get some general suggestions and comments before heading down th
at road. I have to believe that others are dealing with this issue and may
have already found good ans
wers.|||Hi Shelley,
Thanks for your updating
Based on my knowledge, I think the first solution of three is better than
others. However, I am not sure whether it will be the best practice for
your senario, as I have said, I could not define the best practice through
newsgroup. The reason why first one is better than okther two is that the
first one could prevent what might happened while other two just monitor
what happened. When there is a damage on database that could not be
recovered, last two is useless, while I think the first one could prevent
it from happening.
Seocndly, SQL Server has such functionality to track DBA activieties. You'd
better have a look at Books Online or MSDN Online with topic "C2 Auditing".
C2 auditing, which requires that you follow very specific security
policies. You could follow this to make it effective (You need restart your
server). Attention please, opening c2 auditing will have an IMPACT on your
server performance!
-In Query Analyzer
sp_configure 'show',1
go
reconfigure with override
go
sp_configure 'c2',1
reconfigure with override
go
--then restart the server, you will see an trc file on your data directory
now. AGAIN, this kind of writing into disk will have an influence on your
performance
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.sql
Sunday, March 25, 2012
Best practise on Database security
Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HF
Is it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegro ups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>
|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegro ups.com...
>
>
>
>
> - Show quoted text -
We are using SQL Server 2000
|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegr oups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> We are using SQL Server 2000
>
sql
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HF
Is it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegro ups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>
|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegro ups.com...
>
>
>
>
> - Show quoted text -
We are using SQL Server 2000
|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegr oups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> We are using SQL Server 2000
>
sql
Best practise on Database security
Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> We are using SQL Server 2000
>
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> We are using SQL Server 2000
>
Best practise on Database security
Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
> > Hi All,
> > In our development server, everyone (Developers) are member of system
> > administrator (SA).
> > So on development server, anyone can do all database access.
> > In our production server, there are only two type of account which are
> > SA and Public Account.
> > SA can do all database access i.e.: creating the database, tables, and
> > security accounts, performing backups, and tuning the database.
> > Public account (used by application. passwd is created by SA and
> > encrypted on app setting), they can not execute query directly to
> > sqlserver, they can only run stored procedure provided.
> > Now, i want to develop new procedure to manage account and authority
> > on database.
> > Can anyone tell me, a best practise on this? (Database security)
> > I mean, what account should be provided in development and production
> > svr,
> > and what can each type of account do?
> > Rgds
> > HF- Hide quoted text -
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Is it SQL Server 2000/2005?
>> <harifa...@.gmail.com> wrote in message
>> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>>
>> > Hi All,
>> > In our development server, everyone (Developers) are member of system
>> > administrator (SA).
>> > So on development server, anyone can do all database access.
>> > In our production server, there are only two type of account which are
>> > SA and Public Account.
>> > SA can do all database access i.e.: creating the database, tables, and
>> > security accounts, performing backups, and tuning the database.
>> > Public account (used by application. passwd is created by SA and
>> > encrypted on app setting), they can not execute query directly to
>> > sqlserver, they can only run stored procedure provided.
>> > Now, i want to develop new procedure to manage account and authority
>> > on database.
>> > Can anyone tell me, a best practise on this? (Database security)
>> > I mean, what account should be provided in development and production
>> > svr,
>> > and what can each type of account do?
>> > Rgds
>> > HF- Hide quoted text -
>> - Show quoted text -
> We are using SQL Server 2000
>
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
> > Hi All,
> > In our development server, everyone (Developers) are member of system
> > administrator (SA).
> > So on development server, anyone can do all database access.
> > In our production server, there are only two type of account which are
> > SA and Public Account.
> > SA can do all database access i.e.: creating the database, tables, and
> > security accounts, performing backups, and tuning the database.
> > Public account (used by application. passwd is created by SA and
> > encrypted on app setting), they can not execute query directly to
> > sqlserver, they can only run stored procedure provided.
> > Now, i want to develop new procedure to manage account and authority
> > on database.
> > Can anyone tell me, a best practise on this? (Database security)
> > I mean, what account should be provided in development and production
> > svr,
> > and what can each type of account do?
> > Rgds
> > HF- Hide quoted text -
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Is it SQL Server 2000/2005?
>> <harifa...@.gmail.com> wrote in message
>> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>>
>> > Hi All,
>> > In our development server, everyone (Developers) are member of system
>> > administrator (SA).
>> > So on development server, anyone can do all database access.
>> > In our production server, there are only two type of account which are
>> > SA and Public Account.
>> > SA can do all database access i.e.: creating the database, tables, and
>> > security accounts, performing backups, and tuning the database.
>> > Public account (used by application. passwd is created by SA and
>> > encrypted on app setting), they can not execute query directly to
>> > sqlserver, they can only run stored procedure provided.
>> > Now, i want to develop new procedure to manage account and authority
>> > on database.
>> > Can anyone tell me, a best practise on this? (Database security)
>> > I mean, what account should be provided in development and production
>> > svr,
>> > and what can each type of account do?
>> > Rgds
>> > HF- Hide quoted text -
>> - Show quoted text -
> We are using SQL Server 2000
>
Tuesday, March 20, 2012
Best practices for accessing a sql 2005 db on the SAME box as IIS 6.0 serving asp.net 2.0
I am re-posting this from the security Forum where it remains un-answered.OK, Here's the set up.I have a Windows 2003 box, soon to have SSL installedOn it is IIS 6.0, SQL 2005 Standard Edition (5Cal user lic) SOON I'll have a prod enviornemnt where a web app being served by IIS is accessing SQL. I can go into SQL and set up a user account, call it MyAppSQLAcess, and code that into the connectionn string and lock it down to the tables/db it has access to. Or I can do it w/windows authentication, or I can do it a number of other ways, the question is this:What is the best way for an asp.net app being server by IIS 6.0 to access data from SQL 2005 server when they are all on the same BOX? WRT Speed and security? Thanks Dan
You really should take some time to read the information in the link below which is also in SQL 2005 Books Online. It'll help you in making informed decisions.
http://msdn2.microsoft.com/en-us/library/bb283235.aspx
Best of Luck!
||| Thanks!
Nice link. Here is also another short concise article I found helpful
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
Best practice-backup-
I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.
Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer
|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.
|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>
|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserv...sql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
[vbcol=seagreen]
> script
|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK =
'g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK =
'g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK =
'g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
[vbcol=seagreen]
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserv...sql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net... [vbcol=seagreen]
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK =
> 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK =
> 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK =
> 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.
Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer
|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.
|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>
|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserv...sql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
[vbcol=seagreen]
> script
|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK =
'g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK =
'g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK =
'g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
[vbcol=seagreen]
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserv...sql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net... [vbcol=seagreen]
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK =
> 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK =
> 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK =
> 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
Best practice-backup-
I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlser...ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
[vbcol=seagreen]
> script|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK =
'g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK =
'g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK =
'g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
[vbcol=seagreen]
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlser...ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log
backup sequence.
If not, set the database to simple recovery mode and you don't have to do TR
UNCATE_ONLY.
Also, your scripts only includes the system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...[vbcol=seagreen]
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK =
> 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK =
> 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK =
> 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlser...ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
[vbcol=seagreen]
> script|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK =
'g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK =
'g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK =
'g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
[vbcol=seagreen]
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlser...ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log
backup sequence.
If not, set the database to simple recovery mode and you don't have to do TR
UNCATE_ONLY.
Also, your scripts only includes the system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...[vbcol=seagreen]
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK =
> 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK =
> 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK =
> 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>
Best practice-backup-
I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>>I am confused because I see two options on the backup plan of SQL 2000.
>> Under SQL Server Enterprise Manager - SQL Agent:
>> 1.Security Backup (copia de seguridad)
>> Here I can setup a simple copy, at first it allows me only to select the
>> path and filename. Then now I was able to right click and select the
>> schedulle and all.
>> 2.Database Maintaince Plan (Planes de mantenimento de la base)
>> Here I can do more then just backup. And what I like best is that it has
>> option to automtacly remove files older the x day plus the schedulle.
>> Is there a link a good SQL backup plan.
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
> script
>> for Full backup|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK ='g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK ='g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK ='g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK => 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK => 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK => 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>> Yes, Hari if you could post any samples of scripts.
>> Well I found something in the net witch I will give it a try.
>> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
>> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>>I am confused because I see two options on the backup plan of SQL 2000.
>> Under SQL Server Enterprise Manager - SQL Agent:
>> 1.Security Backup (copia de seguridad)
>> Here I can setup a simple copy, at first it allows me only to select the
>> path and filename. Then now I was able to right click and select the
>> schedulle and all.
>> 2.Database Maintaince Plan (Planes de mantenimento de la base)
>> Here I can do more then just backup. And what I like best is that it has
>> option to automtacly remove files older the x day plus the schedulle.
>> Is there a link a good SQL backup plan.
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
> script
>> for Full backup|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK ='g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK ='g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK ='g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK => 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK => 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK => 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>> Yes, Hari if you could post any samples of scripts.
>> Well I found something in the net witch I will give it a try.
>> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
>> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup
Monday, March 19, 2012
Best practice question
I have a aspx page that needs data from sqlexpress. When I installed sqlexpress, I installed it with "mixed-mode" security. When a user visit's my page, by default the username is IUSR_MyMachine. So, I added this user to the SQLServer2005MSSSQLUSers$MyMachine$SQLEXPRESS group. I have a connection string that looks like:
"Data Source=MyServer\sqlexpress;Initial Catalog=MyDB;Integrated Security=True;Pooling=True"
At this point I can go on my merry way and log in and do my thing. My question is this, is this the right way to do this? Should I be adding IUSR to the sql group? Should I set up a sql user instead? What's the best practice for connecting to a db from an aspx page?
Any insight would be great.
Thanks ... Ed
This is a repost. I never got a response the first time.
Of course I am leaving out all the gory details of mapping a specific aspx page to a specific user, this can be bloody difficult. One way to do it is to create a .NET component hosted in COM+ and set the COM+ package to run as a specific user (having the aspx page make calls to the component). You can also twiggle around with the web.config file in aspx to do this mapping.
However, since you already have all your desired users mapped to IUSR_MyMachine by default, then you can just skip the SQLReader part, just add IUSR_MyMachine as login to only the MyDB database and only grant read access and you are set. This is assuming you are ok for any user that hits your web site to read from the SQL database.|||Thanks for the response. You've given me a lot to think about. I appreciate the thoughtful comments.
Later ... Ed
Best Practice for SQL Server security
Our database section is relatively new. The SQL Servers were setup
before our creation and the security is our first area of concern.
Currently, there is a .NET 1.1 COM layer that handles all database
access. It is using a single NT domain account with read/write to all
the databases and execute on the user stored procedures. From what I'm
told, all the business rules for applications (mostly web apps) are
handled in the COM layer.
Our section feels that this is not a secured model from a database
perspective. We want each application to have a unique login to SQL.
Each application would have read/write/execute on its main database as
it does currently, however data access to other databases would be
tightly controlled by the allowing only enough privileges to accomplish
the business objective.
We are hitting massive battles from a small section of our
application developers who insist that the current model is most
secure. Our stance is that we don't trust the middle tier because of
the amount of human coding involved and the lack of auditability on
database security. They have proposed a solution where each COM object
has it's own unique ID, but any application can call any COM object
with read/write/execute in any database - this is not acceptable to us.
My question is - what is the best practice? What are you doing for
security with a "trusted" middle-tier.Hi
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1165926885.850901.28250@.n67g2000cwd.googlegroups.com...
> Our database section is relatively new. The SQL Servers were setup
> before our creation and the security is our first area of concern.
> Currently, there is a .NET 1.1 COM layer that handles all database
> access. It is using a single NT domain account with read/write to all
> the databases and execute on the user stored procedures. From what I'm
> told, all the business rules for applications (mostly web apps) are
> handled in the COM layer.
> Our section feels that this is not a secured model from a database
> perspective. We want each application to have a unique login to SQL.
> Each application would have read/write/execute on its main database as
> it does currently, however data access to other databases would be
> tightly controlled by the allowing only enough privileges to accomplish
> the business objective.
> We are hitting massive battles from a small section of our
> application developers who insist that the current model is most
> secure. Our stance is that we don't trust the middle tier because of
> the amount of human coding involved and the lack of auditability on
> database security. They have proposed a solution where each COM object
> has it's own unique ID, but any application can call any COM object
> with read/write/execute in any database - this is not acceptable to us.
> My question is - what is the best practice? What are you doing for
> security with a "trusted" middle-tier.
>|||Thanks, that document helps. I think I'm more looking for an overall
approach to security. Are there other DBAs out there that are fully
trusting the COM objects? I confess I don't know much about COM, but
I feel really uneasy about allowing an object out of our control the
access that it has.
Even in their updated model (each COM has it's own Login) we still
wouldn't know if application A is using database A, database B,
database C, etc.. and which tables in those other databases.
I need ammo to go to our next meeting and show them how this isn't
secure from our perspective. We also have SQL Log Rescue, so we have
the ability to scour the logs for info - but we can't tell who is doing
what because it all shows that it's the COM account accessing the db.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> http://vyaskn.tripod.com/sql_server...t_practices.htm --s
ecurity
> best practices
>
>
>
>
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1165926885.850901.28250@.n67g2000cwd.googlegroups.com...|||Wow. I had the exact discussion recently and would be very interested to
see other opinions and info on this also.
J
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1165934077.707745.266260@.79g2000cws.googlegroups.com...
> Thanks, that document helps. I think I'm more looking for an overall
> approach to security. Are there other DBAs out there that are fully
> trusting the COM objects? I confess I don't know much about COM, but
> I feel really uneasy about allowing an object out of our control the
> access that it has.
> Even in their updated model (each COM has it's own Login) we still
> wouldn't know if application A is using database A, database B,
> database C, etc.. and which tables in those other databases.
> I need ammo to go to our next meeting and show them how this isn't
> secure from our perspective. We also have SQL Log Rescue, so we have
> the ability to scour the logs for info - but we can't tell who is doing
> what because it all shows that it's the COM account accessing the db.
> Uri Dimant wrote:
>|||No one has really weighed in, I'm surprised.
J wrote:[vbcol=seagreen]
> Wow. I had the exact discussion recently and would be very interested to
> see other opinions and info on this also.
> J
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1165934077.707745.266260@.79g2000cws.googlegroups.com...
before our creation and the security is our first area of concern.
Currently, there is a .NET 1.1 COM layer that handles all database
access. It is using a single NT domain account with read/write to all
the databases and execute on the user stored procedures. From what I'm
told, all the business rules for applications (mostly web apps) are
handled in the COM layer.
Our section feels that this is not a secured model from a database
perspective. We want each application to have a unique login to SQL.
Each application would have read/write/execute on its main database as
it does currently, however data access to other databases would be
tightly controlled by the allowing only enough privileges to accomplish
the business objective.
We are hitting massive battles from a small section of our
application developers who insist that the current model is most
secure. Our stance is that we don't trust the middle tier because of
the amount of human coding involved and the lack of auditability on
database security. They have proposed a solution where each COM object
has it's own unique ID, but any application can call any COM object
with read/write/execute in any database - this is not acceptable to us.
My question is - what is the best practice? What are you doing for
security with a "trusted" middle-tier.Hi
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1165926885.850901.28250@.n67g2000cwd.googlegroups.com...
> Our database section is relatively new. The SQL Servers were setup
> before our creation and the security is our first area of concern.
> Currently, there is a .NET 1.1 COM layer that handles all database
> access. It is using a single NT domain account with read/write to all
> the databases and execute on the user stored procedures. From what I'm
> told, all the business rules for applications (mostly web apps) are
> handled in the COM layer.
> Our section feels that this is not a secured model from a database
> perspective. We want each application to have a unique login to SQL.
> Each application would have read/write/execute on its main database as
> it does currently, however data access to other databases would be
> tightly controlled by the allowing only enough privileges to accomplish
> the business objective.
> We are hitting massive battles from a small section of our
> application developers who insist that the current model is most
> secure. Our stance is that we don't trust the middle tier because of
> the amount of human coding involved and the lack of auditability on
> database security. They have proposed a solution where each COM object
> has it's own unique ID, but any application can call any COM object
> with read/write/execute in any database - this is not acceptable to us.
> My question is - what is the best practice? What are you doing for
> security with a "trusted" middle-tier.
>|||Thanks, that document helps. I think I'm more looking for an overall
approach to security. Are there other DBAs out there that are fully
trusting the COM objects? I confess I don't know much about COM, but
I feel really uneasy about allowing an object out of our control the
access that it has.
Even in their updated model (each COM has it's own Login) we still
wouldn't know if application A is using database A, database B,
database C, etc.. and which tables in those other databases.
I need ammo to go to our next meeting and show them how this isn't
secure from our perspective. We also have SQL Log Rescue, so we have
the ability to scour the logs for info - but we can't tell who is doing
what because it all shows that it's the COM account accessing the db.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> http://vyaskn.tripod.com/sql_server...t_practices.htm --s
ecurity
> best practices
>
>
>
>
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1165926885.850901.28250@.n67g2000cwd.googlegroups.com...|||Wow. I had the exact discussion recently and would be very interested to
see other opinions and info on this also.
J
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1165934077.707745.266260@.79g2000cws.googlegroups.com...
> Thanks, that document helps. I think I'm more looking for an overall
> approach to security. Are there other DBAs out there that are fully
> trusting the COM objects? I confess I don't know much about COM, but
> I feel really uneasy about allowing an object out of our control the
> access that it has.
> Even in their updated model (each COM has it's own Login) we still
> wouldn't know if application A is using database A, database B,
> database C, etc.. and which tables in those other databases.
> I need ammo to go to our next meeting and show them how this isn't
> secure from our perspective. We also have SQL Log Rescue, so we have
> the ability to scour the logs for info - but we can't tell who is doing
> what because it all shows that it's the COM account accessing the db.
> Uri Dimant wrote:
>|||No one has really weighed in, I'm surprised.
J wrote:[vbcol=seagreen]
> Wow. I had the exact discussion recently and would be very interested to
> see other opinions and info on this also.
> J
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1165934077.707745.266260@.79g2000cws.googlegroups.com...
Monday, February 13, 2012
Beginner's questions
Hello,
I have two database servers that I am interested in sending messeges between them. I am not interested in security or encryption at this point, only a simple message sending and recieving.
Can someone please provide simple scripts for object setup (certificates, queues etc) and message send and recieve ?
a link to such a script will be great as well. I am just a bit lost understanding all the new concepts (certificates, service, service binding.....)
Thank you
I.
The Service Listing Manager tool from https://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx can be used to set this up. It will do all the necesary settings for you, including creating and configuring the endpoints, setting up the routes and setting up security. Give it a try and see if still things look complex after you use it :)
HTH,
~ Remus
I installed the tool and followed the instructions but all it did was to create a document with the service name and endpoint. what do I do from here ?
Thanks
I.
|||Open the created document, connect to the other machine and select the 'Import' option.
HTH,
~ Remus
Thanks Remus,
That was very helpful. now - I know how to send and recieve messages localy on the same instance. how do I send and recieve messages between two different instances ?
Thank you
I.
|||The tool does it. Repeat the same steps as for local instance, but simply connect to a different instance when running the Import step. It will do all the necesary setup steps (creat an endpoint, setup endpoint security, setup transport routes in the database etc).
HTH,
~Remus
No differences there. Servicve Broker enables an application designed localy to work distributed w/o any changes.
HTH,
~ Remus
|||
then I guess I have a syntax error...
I am using this code to send message from one instance to another :
BEGIN TRANSACTION ;
GO
DECLARE @.message XML ;
SET @.message = N'<message>Hello, World!</message>' ;
DECLARE @.conversationHandle UNIQUEIDENTIFIER ;
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE InitiatorService
TO SERVICE 'tcp://TCA02SQL603:4037','3771F429-0111-488C-9D0E-080226B1DD8F'
ON CONTRACT HelloWorldContract ;
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloWorldMessage
(@.message) ;
SET @.message = N'<message>Goodbye!</message>' ;
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloWorldMessage
(@.message) ;
END CONVERSATION @.conversationHandle ;
the message doesn't arrive and in the sys.transmission_queue table I get the error :
'The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.'
|||The error you mention in the sys.transmission_queue indicates that there is no route for the service named 'tcp://TCA02SQL603:4037'. Perhaps you used the route address instead of the service name? BEGIN DIALOG takes in the name of the target service (e.g. 'TargetService').
HTH,
~ Remus
|||
OK, I indeed used the route address instead of the service name so I replaced it :
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE InitiatorService
TO SERVICE 'TargetService','3771F429-0111-488C-9D0E-080226B1DD8F'
ON CONTRACT HelloWorldContract ;
However, I still get the same message. I must specify that in sys.transmission_queue the
to_service_name column = 'TargetService,3771F429-0111-488C-9D0E-080226B1DD8F'
and the 'to_broker_instance' is empty.
|||This means you had a typo in the BEGIN DIALOG statement and you actually used TO SERVICE 'TargetService, 3771F...' instead of 'TargetService','3771F...'. You should see 'TargetService' in to_service_name column and '3771F..' in to_broker_instance column.
Also, make sure the row in sys.transmission_queue is the one from your lastes actual BEGIN DIALOG, and not left there from previous attempts. To clean up failed attempts, use END CONVERSATION ... WITH CLEANUP
HTH,
~ Remus
Thanks Remus, that really helped.
I am getting a different error message now :
Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.
|||I think I deleted your post by mistake.
Ilan D wrote:
ok. for some reason 'End conversation @.CoversationHandle WITH CLEANUP' doesnt clean up the sys.transmission_queue table. any suggestions ?
I tried to make sure that I use ',' between service and instance. it's really hard to tell which error message I got because the table doesn't clean up... but I believe that the error message I am getting right now is 'The private key for the security certificate bound to the database principal (ID 1) is password protected. Password protected private keys are not supported for use with secure dialogs.'
You need to cleanup the particular @.conversationHandle that shows in the conversation_handle column in sys.transmission_queue. You need to do this individually for each conversation you wish to clean up.
The error indicates that the 'dbo' certificate is encrypted by password and the database master key encryption is missing. I assume you used the Service Lsiting Manager tool to setup the certificates. I believe the problem then lieas with 'dbo' having some aditional certificates marked as 'active for begin dialog' that are not the ones deployed by the tool. Lookup in sys.certificates what other certificates are owned by 'dbo' and mark them as not active for begin dialog (ALTER CERTIFICATE [...] WITH ACTIVE FOR BEGIN_DIALOG = OFF, see http://msdn2.microsoft.com/en-us/library/ms189511.aspx)
HTH,
~ Remus
I'd recommend to use certificate based authentication for the endpoints, is easier to get it right.
The error you see is because the NTLM/Kerberos authentication ends up with the anonymous login. The best document to solve these kind of issues is this: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx
Remeber that the accounts being authenticated are those of that are actually running the two SQL Server services. If the SQL Server instances are running as LocalSystem or NETWORK SERVICE, then you must register the SPN 'MSSQLSvc/<machinename>:<brokerport>' for Kerberos to work (use a tool like setspn.exe, available at MS download center).
HTH,
~ Remus
Beginner security questions
Thanks in advance for your patience with my beginner questions.
I have a SQL Server instance setup on my XP desktop (the 120 day Evaluation
Edition - V8.00.194). When I installed it I setup the instance with "Windows
Only" authentication.
I have myself and my wife with XP user accounts and also have a guest user a
ccount active. I created a database for practice, and would like allow eithe
r my wife's account or the guest account to login and access the one practic
e database, with select pri
viledge's only on tables. Ideally from these "user" XP accounts, I would lik
e to allow access to my practice database only through the Query Analyzer Wi
ndow. I don't want to allow these accounts to see other databases or system
tables. I don't want them t
o be able to stratup Enterprise Manager or any tool other than Query Analyze
r for the one database.
What are the steps I need to follow to create permissions? After I do these
steps, I want to log in through the guest account and see that my security s
etup works.
I have read the chapters in my textbook on security, but still don't really
get it. I guess I need a simpler example that I can practice for myself.
Your help in teaching a newbie is greatly appreciated.If your goal is to learn how security works by playing with it, the
quickest way is to enable mixed-mode authentication. Then you can
create SQL logins (which are unavailable in Windows Only mode) and
assign them permissions, which you can then test using the Query
Analyzer, which lets you open multiple connections based on different
logins. Once you've tested and debugged security using SQL logins to
mimic your eventual Windows logins, you can delete them and assign
Windows logins to the roles you've created and set your security mode
back to Windows only for production. Not sure which textbook you are
using, but this site has lots of useful resources:
[url]http://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp[/ur
l]
--Mary
On Tue, 30 Mar 2004 12:56:10 -0800, "Jack Wachtler"
<jack_wachtler@.comcast.net> wrote:
>Thanks in advance for your patience with my beginner questions.
>I have a SQL Server instance setup on my XP desktop (the 120 day Evaluation
Edition - V8.00.194). When I installed it I setup the instance with "Window
s Only" authentication.
>I have myself and my wife with XP user accounts and also have a guest user account
active. I created a database for practice, and would like allow either my wife's acc
ount or the guest account to login and access the one practice database, with select
pr
iviledge's only on tables. Ideally from these "user" XP accounts, I would li
ke to allow access to my practice database only through the Query Analyzer W
indow. I don't want to allow these accounts to see other databases or system
tables. I don't want them
to be able to stratup Enterprise Manager or any tool other than Query Analyzer for the one
database.
>What are the steps I need to follow to create permissions? After I do these
steps, I want to log in through the guest account and see that my security
setup works.
>I have read the chapters in my textbook on security, but still don't really
get it. I guess I need a simpler example that I can practice for myself.
>Your help in teaching a newbie is greatly appreciated.|||Since you've installed with "Windows Only" you may have a few steps to take
before you can allow other users to access the database. Normally this
option works if the SQL Server is part of a domain, which I presume your
machines are not part of. You could try to create a user account on the SQL
Server that has the same name as the user your wife is using on her XP, with
the same password. This might work (I'm not sure).
An other option is to change the security to "Mixed mode", and create SQL
Server accounts. You may be able to change the licensing by way of the "SQL
Server 2000 Licensing" icon on the control panel. However the "Mixed mode"
option may be grayed out. In that case you could do one of two things:
1. Reinstall sql server in mixed mode (remember to back up your database
first)
OR
2. You could do this (cut from another posting found on Google):
Change the LoginMode value of the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer registry key
(for default instance).
1 = Windows Only
2 = SQL Server and Windows
Sincerely
Svein Terje Gaup
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:01A128AE-2A11-4719-BB37-DC8B683D662A@.microsoft.com...
> Thanks in advance for your patience with my beginner questions.
> I have a SQL Server instance setup on my XP desktop (the 120 day
Evaluation Edition - V8.00.194). When I installed it I setup the instance
with "Windows Only" authentication.
> I have myself and my wife with XP user accounts and also have a guest user
account active. I created a database for practice, and would like allow
either my wife's account or the guest account to login and access the one
practice database, with select priviledge's only on tables. Ideally from
these "user" XP accounts, I would like to allow access to my practice
database only through the Query Analyzer Window. I don't want to allow these
accounts to see other databases or system tables. I don't want them to be
able to stratup Enterprise Manager or any tool other than Query Analyzer for
the one database.
> What are the steps I need to follow to create permissions? After I do
these steps, I want to log in through the guest account and see that my
security setup works.
> I have read the chapters in my textbook on security, but still don't
really get it. I guess I need a simpler example that I can practice for
myself.
> Your help in teaching a newbie is greatly appreciated.
I have a SQL Server instance setup on my XP desktop (the 120 day Evaluation
Edition - V8.00.194). When I installed it I setup the instance with "Windows
Only" authentication.
I have myself and my wife with XP user accounts and also have a guest user a
ccount active. I created a database for practice, and would like allow eithe
r my wife's account or the guest account to login and access the one practic
e database, with select pri
viledge's only on tables. Ideally from these "user" XP accounts, I would lik
e to allow access to my practice database only through the Query Analyzer Wi
ndow. I don't want to allow these accounts to see other databases or system
tables. I don't want them t
o be able to stratup Enterprise Manager or any tool other than Query Analyze
r for the one database.
What are the steps I need to follow to create permissions? After I do these
steps, I want to log in through the guest account and see that my security s
etup works.
I have read the chapters in my textbook on security, but still don't really
get it. I guess I need a simpler example that I can practice for myself.
Your help in teaching a newbie is greatly appreciated.If your goal is to learn how security works by playing with it, the
quickest way is to enable mixed-mode authentication. Then you can
create SQL logins (which are unavailable in Windows Only mode) and
assign them permissions, which you can then test using the Query
Analyzer, which lets you open multiple connections based on different
logins. Once you've tested and debugged security using SQL logins to
mimic your eventual Windows logins, you can delete them and assign
Windows logins to the roles you've created and set your security mode
back to Windows only for production. Not sure which textbook you are
using, but this site has lots of useful resources:
[url]http://www.microsoft.com/sql/techinfo/administration/2000/security/default.asp[/ur
l]
--Mary
On Tue, 30 Mar 2004 12:56:10 -0800, "Jack Wachtler"
<jack_wachtler@.comcast.net> wrote:
>Thanks in advance for your patience with my beginner questions.
>I have a SQL Server instance setup on my XP desktop (the 120 day Evaluation
Edition - V8.00.194). When I installed it I setup the instance with "Window
s Only" authentication.
>I have myself and my wife with XP user accounts and also have a guest user account
active. I created a database for practice, and would like allow either my wife's acc
ount or the guest account to login and access the one practice database, with select
pr
iviledge's only on tables. Ideally from these "user" XP accounts, I would li
ke to allow access to my practice database only through the Query Analyzer W
indow. I don't want to allow these accounts to see other databases or system
tables. I don't want them
to be able to stratup Enterprise Manager or any tool other than Query Analyzer for the one
database.
>What are the steps I need to follow to create permissions? After I do these
steps, I want to log in through the guest account and see that my security
setup works.
>I have read the chapters in my textbook on security, but still don't really
get it. I guess I need a simpler example that I can practice for myself.
>Your help in teaching a newbie is greatly appreciated.|||Since you've installed with "Windows Only" you may have a few steps to take
before you can allow other users to access the database. Normally this
option works if the SQL Server is part of a domain, which I presume your
machines are not part of. You could try to create a user account on the SQL
Server that has the same name as the user your wife is using on her XP, with
the same password. This might work (I'm not sure).
An other option is to change the security to "Mixed mode", and create SQL
Server accounts. You may be able to change the licensing by way of the "SQL
Server 2000 Licensing" icon on the control panel. However the "Mixed mode"
option may be grayed out. In that case you could do one of two things:
1. Reinstall sql server in mixed mode (remember to back up your database
first)
OR
2. You could do this (cut from another posting found on Google):
Change the LoginMode value of the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer registry key
(for default instance).
1 = Windows Only
2 = SQL Server and Windows
Sincerely
Svein Terje Gaup
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:01A128AE-2A11-4719-BB37-DC8B683D662A@.microsoft.com...
> Thanks in advance for your patience with my beginner questions.
> I have a SQL Server instance setup on my XP desktop (the 120 day
Evaluation Edition - V8.00.194). When I installed it I setup the instance
with "Windows Only" authentication.
> I have myself and my wife with XP user accounts and also have a guest user
account active. I created a database for practice, and would like allow
either my wife's account or the guest account to login and access the one
practice database, with select priviledge's only on tables. Ideally from
these "user" XP accounts, I would like to allow access to my practice
database only through the Query Analyzer Window. I don't want to allow these
accounts to see other databases or system tables. I don't want them to be
able to stratup Enterprise Manager or any tool other than Query Analyzer for
the one database.
> What are the steps I need to follow to create permissions? After I do
these steps, I want to log in through the guest account and see that my
security setup works.
> I have read the chapters in my textbook on security, but still don't
really get it. I guess I need a simpler example that I can practice for
myself.
> Your help in teaching a newbie is greatly appreciated.
Subscribe to:
Posts (Atom)