Date: 05/06/11     Keywords: database, security

In our last entry, we introduced the concept of code review procedures.  Our first topic to consider in this life cycle is for the developer to take some time to understand the Business Requirements and Functional context.  These two critical tasks should in a perfect world be understood by all dba's in the SDLC of database code, but the developer has a unique opportunity to let his/her code communicate these requirements and context though coding best practices and adequate documentation.  Some items a developer, or a peer can look for in performing these 2 steps are the following:

Satisfying Business Requirements & Functional Context

• Has a knowledgeable user been consulted during the planning/architecture phase of code creation?

• Did the architect make specifications for future growth and change needs of the application?

• Has the developer reviewed the business requirements?

• Do the developer and the business have the same understanding for required performance of the application?

• Does the reviewer understand the code being reviewed?

• Does your code adhere to corporate coding specifications (Yes, this is a business requirement, too)

• At what layer in your business environment does the code execute?

• Does the piece of code functionally achieve the stakeholder's need as documented in the project charter ?

• What is the data size and volume worked with in this code?

• What are the data archival requirements?

• Have company security policies been complied with?

• How will the application or change be installed and configured?

• By what method will the development team preserve and version the code and objects affected?

adina_atl

Source: http://sqlserver.livejournal.com/74884.html

Date: 05/06/11     Keywords: database, security

In our last entry, we introduced the concept of code review procedures.  Our first topic to consider in this life cycle is for the developer to take some time to understand the Business Requirements and Functional context.  These two critical tasks should in a perfect world be understood by all dba's in the SDLC of database code, but the developer has a unique opportunity to let his/her code communicate these requirements and context though coding best practices and adequate documentation.  Some items a developer, or a peer can look for in performing these 2 steps are the following:

Satisfying Business Requirements & Functional Context

• Has a knowledgeable user been consulted during the planning/architecture phase of code creation?

• Did the architect make specifications for future growth and change needs of the application?

• Has the developer reviewed the business requirements?

• Do the developer and the business have the same understanding for required performance of the application?

• Does the reviewer understand the code being reviewed?

• Does your code adhere to corporate coding specifications (Yes, this is a business requirement, too)

• At what layer in your business environment does the code execute?

• Does the piece of code functionally achieve the stakeholder's need as documented in the project charter ?

• What is the data size and volume worked with in this code?

• What are the data archival requirements?

• Have company security policies been complied with?

• How will the application or change be installed and configured?

• By what method will the development team preserve and version the code and objects affected?

adina_atl

Source: https://sqlserver.livejournal.com/74884.html

Date: 04/18/11     Keywords: security

Does your organization use any code review procedures when promoting code from Development through to Production?  If you work for a publicly-held corporation, you know this all to well.  If you work for a privately-held company, you can benefit from this procedure as well.  Wether you are a developer wanting to make sure all your i's are dotted and all your t's are crossed, or a DBA in operations getting ready to deploy new code, solid code review procedures can really save your butt from major mistakes that can cost you hours of work.

First, let's get a general idea of what a code review life cycle will look like and who is generally involved.  

1. Unit Test - Validate Business Requirements and Functional Context
2. Unit Test - Validate Syntax, Entry and Exit points
3. Unit Test & QA - Consistency - Perform Version Control & Transactional Analysis
4. QA & Prod - Security - Permissions, Back-out and Error Handling
5. QA & Prod - Performance - Validate Memory Usage, Thread Usage and Control Structure usage
6.  Prod - Documentation - Is there a header, change log, and comments ?  Are deployment instructions required and included?

Source: http://sqlserver.livejournal.com/74700.html

Date: 04/18/11     Keywords: security

Does your organization use any code review procedures when promoting code from Development through to Production?  If you work for a publicly-held corporation, you know this all to well.  If you work for a privately-held company, you can benefit from this procedure as well.  Wether you are a developer wanting to make sure all your i's are dotted and all your t's are crossed, or a DBA in operations getting ready to deploy new code, solid code review procedures can really save your butt from major mistakes that can cost you hours of work.

First, let's get a general idea of what a code review life cycle will look like and who is generally involved.  

1. Unit Test - Validate Business Requirements and Functional Context
2. Unit Test - Validate Syntax, Entry and Exit points
3. Unit Test & QA - Consistency - Perform Version Control & Transactional Analysis
4. QA & Prod - Security - Permissions, Back-out and Error Handling
5. QA & Prod - Performance - Validate Memory Usage, Thread Usage and Control Structure usage
6.  Prod - Documentation - Is there a header, change log, and comments ?  Are deployment instructions required and included?

Source: https://sqlserver.livejournal.com/74700.html

Date: 03/18/11     Keywords: no keywords

In our last two topics, we discussed gathering the space statistics.

Now we need to devise a procedure called sp_check_usage that will check our statistics and calculate when we expect to run out of space.  We will also use a 2 new tables called drive_check_log and server_space_report to record our data for reporting purposes.  For the first day, we will not have any data to report because we will need a second day's worth of statistics to compare the data against.

Now the new procedure will make a couple of passes at the stored data, use some temp tables to do a comparison, then will write a report you can send or review.

CREATE PROCEDURE [dbo].[sp_check_usage]
as

set nocount on

declare @since_date datetime,
@rc1 INT,
@files INT,
@cmd VARCHAR(256),
@datfile1 VARCHAR(128),
@subject nvarchar(500)

set @since_date = dateadd(d,-7,getdate())

SELECT @datfile1 = '\RPT' + CONVERT(CHAR(8),GETDATE(),112) + '.CSV'

-- group by days
select
t1.drive,
datestamp=convert(varchar,datestamp,111),
last_sample_id=max(t2.id),
MBFreeSpace= sum(MBFreeSpace) / count(*),
sample_count = count(*)
into #x1
from free_space_log t1 inner join drive_check_log t2
on t1.id = t2.id
where t2.datestamp >= @since_date
group by t1.drive, convert(varchar,datestamp,111)

-- uncomment to debug
--select * from #x1

-- get a days growth
select
t1.drive,
avg_per_day =
sum(t2.MBFreeSpace - t1.MBFreeSpace ) / count(*)
into #x3
from #x1 t1 inner join #x1 t2 on
t1.drive = t2.drive and
t1.datestamp = convert(varchar,dateadd(d,1,t2.datestamp),111)
group by
t1.drive

select * from #x3

-- get the latest sample date
select
t1.drive,
last_sample_id=max(t2.id),
last_sample_date=max(t2.datestamp),
CurrentMBFree=convert(float,0)
into #x2
from free_space_log t1 (nolock) inner join drive_check_log t2 (nolock)
on t1.id = t2.id
group by
t1.drive

-- set the current free space

update #x2 set
CurrentMBFree = MBFreeSpace
from free_space_log t1 (nolock)
where
#x2.drive = t1.drive and
#x2.last_sample_id = t1.id

select * from #x2

--This is where the procedure produces the output

truncate table server_space_report

insert server_space_report ( drive,
last_sample_date,
avg_per_day,
CurrentMBFree,
days_left)
select
#x2.drive,
#x2.last_sample_date,
#x3.avg_per_day,
#x2.CurrentMBFree,
days_left =
case when #x3.avg_per_day = 0 then 'low or no usage'
when #x3.avg_per_day < 0 then 'negative usage'
else 'Out of space in ' +
replicate(' ', 6 - datalength(convert(varchar, round((#x2.CurrentMBFree / #x3.avg_per_day),0)))) +
convert(varchar, round((#x2.CurrentMBFree / #x3.avg_per_day),0)) + ' days'
end
from #x2
inner join #x3 on
#x2.drive = #x3.drive
order by #x2.drive

-- uncomment to debug
-- select * from server_space_report

set nocount off
GO


** Please be responsible with free code. Test and check before implementing in a production environment

Source: http://sqlserver.livejournal.com/74374.html

Date: 03/18/11     Keywords: no keywords

In our last two topics, we discussed gathering the space statistics.

Now we need to devise a procedure called sp_check_usage that will check our statistics and calculate when we expect to run out of space.  We will also use a 2 new tables called drive_check_log and server_space_report to record our data for reporting purposes.  For the first day, we will not have any data to report because we will need a second day's worth of statistics to compare the data against.

Now the new procedure will make a couple of passes at the stored data, use some temp tables to do a comparison, then will write a report you can send or review.

CREATE PROCEDURE [dbo].[sp_check_usage]
as

set nocount on

declare @since_date datetime,
@rc1 INT,
@files INT,
@cmd VARCHAR(256),
@datfile1 VARCHAR(128),
@subject nvarchar(500)

set @since_date = dateadd(d,-7,getdate())

SELECT @datfile1 = '\RPT' + CONVERT(CHAR(8),GETDATE(),112) + '.CSV'

-- group by days
select
t1.drive,
datestamp=convert(varchar,datestamp,111),
last_sample_id=max(t2.id),
MBFreeSpace= sum(MBFreeSpace) / count(*),
sample_count = count(*)
into #x1
from free_space_log t1 inner join drive_check_log t2
on t1.id = t2.id
where t2.datestamp >= @since_date
group by t1.drive, convert(varchar,datestamp,111)

-- uncomment to debug
--select * from #x1

-- get a days growth
select
t1.drive,
avg_per_day =
sum(t2.MBFreeSpace - t1.MBFreeSpace ) / count(*)
into #x3
from #x1 t1 inner join #x1 t2 on
t1.drive = t2.drive and
t1.datestamp = convert(varchar,dateadd(d,1,t2.datestamp),111)
group by
t1.drive

select * from #x3

-- get the latest sample date
select
t1.drive,
last_sample_id=max(t2.id),
last_sample_date=max(t2.datestamp),
CurrentMBFree=convert(float,0)
into #x2
from free_space_log t1 (nolock) inner join drive_check_log t2 (nolock)
on t1.id = t2.id
group by
t1.drive

-- set the current free space

update #x2 set
CurrentMBFree = MBFreeSpace
from free_space_log t1 (nolock)
where
#x2.drive = t1.drive and
#x2.last_sample_id = t1.id

select * from #x2

--This is where the procedure produces the output

truncate table server_space_report

insert server_space_report ( drive,
last_sample_date,
avg_per_day,
CurrentMBFree,
days_left)
select
#x2.drive,
#x2.last_sample_date,
#x3.avg_per_day,
#x2.CurrentMBFree,
days_left =
case when #x3.avg_per_day = 0 then 'low or no usage'
when #x3.avg_per_day < 0 then 'negative usage'
else 'Out of space in ' +
replicate(' ', 6 - datalength(convert(varchar, round((#x2.CurrentMBFree / #x3.avg_per_day),0)))) +
convert(varchar, round((#x2.CurrentMBFree / #x3.avg_per_day),0)) + ' days'
end
from #x2
inner join #x3 on
#x2.drive = #x3.drive
order by #x2.drive

-- uncomment to debug
-- select * from server_space_report

set nocount off
GO


** Please be responsible with free code. Test and check before implementing in a production environment

Source: https://sqlserver.livejournal.com/74374.html

Date: 03/11/11     Keywords: sql

 In our last entry we talked about getting datafile usage in SQL Server.  Today, we'll implement sp_file_space in another stored procedure that combines it with the extended stored procedure xp_fideddrives to calculate the free space and stores the data in two standard tables.

CREATE PROCEDURE [dbo].[sp_log_spaceused]
as
create table #freespace
(
drive char(1) null,
MBfreespace bigint null
)


set nocount on

delete from #freespace
-- log this servers current space used
insert into file_space_log exec.master.dbo.sp_file_space

-- log the freespace
insert into #freespace
(
drive,
MBfreespace
)
exec master.dbo.xp_fixeddrives

-- server_drive_space insert
insert into free_space_log
select
drive,
MBfreespace
from #freespace


GO


** Please be responsible with free code.  Test and check before implementing in a production environment
 

Source: http://sqlserver.livejournal.com/73862.html

Date: 03/11/11     Keywords: sql

 In our last entry we talked about getting datafile usage in SQL Server.  Today, we'll implement sp_file_space in another stored procedure that combines it with the extended stored procedure xp_fideddrives to calculate the free space and stores the data in two standard tables.

CREATE PROCEDURE [dbo].[sp_log_spaceused]
as
create table #freespace
(
drive char(1) null,
MBfreespace bigint null
)


set nocount on

delete from #freespace
-- log this servers current space used
insert into file_space_log exec.master.dbo.sp_file_space

-- log the freespace
insert into #freespace
(
drive,
MBfreespace
)
exec master.dbo.xp_fixeddrives

-- server_drive_space insert
insert into free_space_log
select
drive,
MBfreespace
from #freespace


GO


** Please be responsible with free code.  Test and check before implementing in a production environment
 

Source: https://sqlserver.livejournal.com/73862.html

Date: 03/10/11     Keywords: database

 We create a database called ADMIN which stores our administrative information such as file space statistics. We use a combination of extended stored procedures and publicly-available code to log these statistics. Here is a samples:

/* Get current space statistics. You can run this and store the results in a holding table. */

CREATE PROCEDURE [dbo].[sp_file_space] @server_name sysname, @id int
as

declare @dbname sysname
declare @cmd varchar(700),
@lname_len int,
@fname_len int,
@fgroup_len int

set nocount on

select @cmd = 'use [?] select ' + convert(varchar,@id) + ',''' + rtrim(@server_name) + ''',db_name(),logical_name = name,fileid,upper(substring(filename,1,1)),filename,
filegroup = filegroup_name(groupid),
size_in_KB = size * 8,
maxsize_in_KB = (case maxsize when -1 then 0
else
maxsize * 8 end),
growth = (case status & 0x100000 when 0x100000 then growth else growth * 8 end),
KB_growth_flag = (case status & 0x100000 when 0x100000 then 0 else 1 end) ,
usage = (case status & 0x40 when 0x40 then ''Log Only'' else ''Data Only'' end)
from sysfiles order by fileid'


exec sp_MSforeachdb @command1=@cmd

return 0


GO

** Please be responsible with free code. Test and check before implementing in a production environment

Source: http://sqlserver.livejournal.com/73512.html

Date: 03/10/11     Keywords: database

 We create a database called ADMIN which stores our administrative information such as file space statistics. We use a combination of extended stored procedures and publicly-available code to log these statistics. Here is a samples:

/* Get current space statistics. You can run this and store the results in a holding table. */

CREATE PROCEDURE [dbo].[sp_file_space] @server_name sysname, @id int
as

declare @dbname sysname
declare @cmd varchar(700),
@lname_len int,
@fname_len int,
@fgroup_len int

set nocount on

select @cmd = 'use [?] select ' + convert(varchar,@id) + ',''' + rtrim(@server_name) + ''',db_name(),logical_name = name,fileid,upper(substring(filename,1,1)),filename,
filegroup = filegroup_name(groupid),
size_in_KB = size * 8,
maxsize_in_KB = (case maxsize when -1 then 0
else
maxsize * 8 end),
growth = (case status & 0x100000 when 0x100000 then growth else growth * 8 end),
KB_growth_flag = (case status & 0x100000 when 0x100000 then 0 else 1 end) ,
usage = (case status & 0x40 when 0x40 then ''Log Only'' else ''Data Only'' end)
from sysfiles order by fileid'


exec sp_MSforeachdb @command1=@cmd

return 0


GO

** Please be responsible with free code. Test and check before implementing in a production environment

Source: https://sqlserver.livejournal.com/73512.html

Date: 03/09/11     Keywords: database, sql

 Here's a good question for data environments today.  What methods do you employ to track datafile growth in your SQL Server databases?  Do you use a 3rd-party tool, or do you have a home-brew method?  I'll share my method once we read about other's ideas.

Source: http://sqlserver.livejournal.com/73430.html

Date: 03/09/11     Keywords: database, sql

 Here's a good question for data environments today.  What methods do you employ to track datafile growth in your SQL Server databases?  Do you use a 3rd-party tool, or do you have a home-brew method?  I'll share my method once we read about other's ideas.

Source: https://sqlserver.livejournal.com/73430.html

Date: 02/15/11     Keywords: sql

 It looks like we haven't had much discussion here in quite a while, so as the community owner, I will try to stir some discussion.  

How do you promote your SQL scripts throughout your development, test, and prod environments?

Source: http://sqlserver.livejournal.com/73157.html

Date: 02/15/11     Keywords: sql

 It looks like we haven't had much discussion here in quite a while, so as the community owner, I will try to stir some discussion.  

How do you promote your SQL scripts throughout your development, test, and prod environments?

Source: https://sqlserver.livejournal.com/73157.html

Date: 11/08/10     Keywords: database, security

I have a user who locked himself out of a database because his default db went into suspect mode.  His security policy was nice enough to bar anyone in the Windows Administrators group from logging in to the db.  He says he can't remember the two passwords for the administrative logins currently assigned to System Administrator  role on the server.  Any hope here?  I think he's screwed, personally.

Source: http://sqlserver.livejournal.com/72656.html

Date: 11/08/10     Keywords: database, security

I have a user who locked himself out of a database because his default db went into suspect mode.  His security policy was nice enough to bar anyone in the Windows Administrators group from logging in to the db.  He says he can't remember the two passwords for the administrative logins currently assigned to System Administrator  role on the server.  Any hope here?  I think he's screwed, personally.

Source: https://sqlserver.livejournal.com/72656.html

Date: 09/28/10     Keywords: html, sql

Posting for the benefit of all after personally pulling my hair out on this one...

1) Installed the Oracle Instant Client following these wonderful directions:
http://www.dbatoolz.com/t/installing-oracle-instantclient-basic-and-instantclient-sqlplus-on-win32.html

2) Restarted my SQL server to re-load the ODBC drivers

3) Created linked server

USE master
go
EXEC sp_addlinkedserver @server=N'ORCL_SRVR', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'ORCL_SRVR'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='rpc', @optvalue='true'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='collation compatible', @optvalue='false'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='data access', @optvalue='true'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='rpc out', @optvalue='false'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='use remote collation', @optvalue='true'
go
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ORCL_SRVR', @useself='FALSE', @rmtuser=N'system', @rmtpassword=N'my password'
go
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname=N'ORCL_SRVR')
PRINT N'<<< CREATED LINKED SERVER ORCL_SRVR >>>'
ELSE
PRINT N'<<< FAILED CREATING LINKED SERVER ORCL_SRVR >>>'
go

Source: http://sqlserver.livejournal.com/72406.html

Date: 09/28/10     Keywords: html, sql

Posting for the benefit of all after personally pulling my hair out on this one...

1) Installed the Oracle Instant Client following these wonderful directions:
http://www.dbatoolz.com/t/installing-oracle-instantclient-basic-and-instantclient-sqlplus-on-win32.html

2) Restarted my SQL server to re-load the ODBC drivers

3) Created linked server

USE master
go
EXEC sp_addlinkedserver @server=N'ORCL_SRVR', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'ORCL_SRVR'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='rpc', @optvalue='true'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='collation compatible', @optvalue='false'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='data access', @optvalue='true'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='rpc out', @optvalue='false'
go
EXEC sp_serveroption @server=N'ORCL_SRVR', @optname='use remote collation', @optvalue='true'
go
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ORCL_SRVR', @useself='FALSE', @rmtuser=N'system', @rmtpassword=N'my password'
go
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname=N'ORCL_SRVR')
PRINT N'<<< CREATED LINKED SERVER ORCL_SRVR >>>'
ELSE
PRINT N'<<< FAILED CREATING LINKED SERVER ORCL_SRVR >>>'
go

Source: https://sqlserver.livejournal.com/72406.html

Date: 11/18/09     Keywords: asp, sql, security, web, microsoft

http://technet.microsoft.com/en-us/sqlserver/dd353197.aspx

Has anyone implemented security using the LOGON TRIGGER that came out with SQL Server 2005 SP2?

I'm just curious if anyone has setup this feature to protect their SQL Server from attack through their web servers.

Source: http://sqlserver.livejournal.com/71849.html

Date: 11/18/09     Keywords: asp, sql, security, web, microsoft

http://technet.microsoft.com/en-us/sqlserver/dd353197.aspx

Has anyone implemented security using the LOGON TRIGGER that came out with SQL Server 2005 SP2?

I'm just curious if anyone has setup this feature to protect their SQL Server from attack through their web servers.

Source: https://sqlserver.livejournal.com/71849.html