Author: sqldba

Posted on by — Leave a comment

SQL Server Security best practices

  • Minimize the number of SQL Server logins.

  • Use Windows group logins to simplify ongoing management where possible.

  • Disable logins rather than dropping them if there is any chance that they will be needed again.

  • Ensure that expiry dates are applied to logins that are created for temporary purposes.

  • Use fixed server-level roles to delegate server-level management responsibility, and only create user-defined server-level roles if your specific administrative delegation solution requires them.

  • Disable the guest user in user databases unless you specifically require guest access.

  • Aim to grant the minimum number of explicit permissions possible to meet the security requirements, and use membership of roles and inheritance to ensure the correct effective permissions.

  • Ensure every user has only the permission they actually require.
Posted on by — Leave a comment

SQL Server DDL Change Control


-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
--              compatible SQL Server 2005+
-- =============================================

USE [master]
GO


IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


IF EXISTS (SELECT TOP 1 1 FROM sysobjects WITH (NOLOCK) WHERE TYPE = 'U' AND name = 'changelog')
 BEGIN
    PRINT 'master.dbo.changelog exists, check to be sure no differences in table from older version'
 END
ELSE
 BEGIN
CREATE TABLE [dbo].[changelog](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [databasename] [varchar](256) NOT NULL,
 [eventtype] [varchar](50) NOT NULL,
 [objectname] [varchar](256) NOT NULL,
 [objecttype] [varchar](25) NOT NULL,
 [sqlcommand] [varchar](max) NOT NULL,
 [eventdate] [datetime] NOT NULL,
 [loginname] [varchar](256) NOT NULL
)

ALTER TABLE [dbo].[changelog] ADD  CONSTRAINT [df_changelog_eventdate]  DEFAULT (getdate()) FOR [eventdate]

 END
GO


IF  EXISTS (SELECT * FROM sys.objects WITH (NOLOCK) WHERE type = 'P' AND name = 'sp_ObjectChangeAudit' )
DROP PROCEDURE [dbo].[sp_ObjectChangeAudit]
GO

-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
-- =============================================
CREATE PROCEDURE [dbo].[sp_ObjectChangeAudit]
( @databasename varchar(256)
, @eventtype varchar(50)
, @objectname varchar(256)
, @objecttype varchar(25)
, @sqlcommand varchar(max)
, @loginname varchar(256)
)
AS
BEGIN
   SET NOCOUNT ON;

     INSERT INTO [master].[dbo].[changelog]
               ( databasename
               , eventtype
               , objectname
               , objecttype
               , sqlcommand
               , loginname 
               )
          VALUES 
               ( @databasename
               , @eventtype
               , @objectname
               , @objecttype
               , @sqlcommand
               , @loginname 
               )
END --sp_ObjectChangeAudit
GO


IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO

IF EXISTS (SELECT TOP 1 1 FROM [master].[sys].[server_triggers] WITH (NOLOCK) WHERE parent_class_desc = 'SERVER' AND name = N'tr_ObjectChangeAudit') -- sql server 2005+
   DROP TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


-- =============================================
-- Author:      sqlforge.blogspot.com
-- Create date: 20141008
-- Description: audit ddl changes
-- =============================================
CREATE TRIGGER [tr_ObjectChangeAudit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR create_procedure, alter_procedure, drop_procedure, create_table, alter_table, drop_table, create_function, alter_function, drop_function 
AS 
   DECLARE @databasename varchar(256)
         , @eventtype varchar(50)
         , @objectname varchar(256)
         , @objecttype varchar(25)
         , @sqlcommand varchar(max)
         , @loginname varchar(256)
BEGIN
   SET NOCOUNT ON; 
   SET ANSI_PADDING ON;

   DECLARE @data xml
   SET @data = eventdata() 

       SELECT @databasename = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
            , @eventtype = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
            , @objectname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
            , @objecttype = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
            , @sqlcommand = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')
            , @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 

BEGIN TRY
   EXEC [master].[dbo].[sp_ObjectChangeAudit]
        @databasename 
      , @eventtype 
      , @objectname 
      , @objecttype 
      , @sqlcommand
      , @loginname
END TRY
BEGIN CATCH
   PRINT 'SERVER TRIGGER tr_ObjectChangeAudit ' + ERROR_MESSAGE();
END CATCH
      
END --tr_ObjectChangeAudit


GO

DISABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO

ENABLE TRIGGER [tr_ObjectChangeAudit] ON ALL SERVER
GO


/*****************************

IF EXISTS ( SELECT TOP 1 1 FROM sysobjects WHERE type = 'P' AND name = 'TESTME_01234' )
  DROP PROCEDURE TESTME_01234
GO

CREATE PROCEDURE TESTME_01234
AS
BEGIN
   SELECT 1;
END
GO

  DROP PROCEDURE TESTME_01234
GO


SELECT * FROM master.dbo.changelog ORDER BY eventdate DESC
GO
****************************************/
Posted on by — Leave a comment

ODBC: Another 32/64bit MS undocumented “feature”

There are two different ODBC Administrator runtimes on a 64-bit machine.

The first ODBC Manager is used to manage 64-bit data sources, while the second is used to manage 32-bit data sources.

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed. If you are using a 64 bit machine, the default ODBC Manager will be for 64-bit data sources.

If you have a 64bit OS and are trying to access a DSN in your 32bit application and receive the error check to see if you have a DSN configured for the architecture of your application ( 32-bit / 64-bit ).

“ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”
Use this ODBC Manager to Review 64-Bit Data Source Names

c:\windows\system32\odbcad32.exe

Use this ODBC Manager to Review 32-Bit Data Source Names

c:\windows\sysWOW64\odbcad32.exe
Posted on by — Leave a comment

Login History Server Trigger



use master

go


IF NOT EXISTS ( SELECT TOP 1 1 FROM master.sys.sysobjects WHERE type=’U’ AND name=’ServerLoginHistory’ )

 BEGIN

  CREATE TABLE [dbo].[ServerLoginHistory]

  (

   [SystemUser] [varchar](512) NULL,

   [HostName] [varchar](512) NULL,

   [DBUser] [varchar](512) NULL,

   [SPID] [int] NULL,

   [LoginTime] [datetime] NULL,

   [AppName] [varchar](512) NULL,

   [DatabaseName] [varchar](512) NULL

  )

 END

GO


IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = ‘SERVER’ AND name = N’tr_ServerLoginHistory’)

 BEGIN

   DROP TRIGGER [tr_ServerLoginHistory] ON ALL SERVER

 END

GO


CREATE TRIGGER [tr_ServerLoginHistory]

ON ALL SERVER WITH EXECUTE AS ‘sa’

FOR LOGON

AS

BEGIN

   IF ORIGINAL_LOGIN() NOT IN ( ‘NT AUTHORITYSYSTEM’ )

    BEGIN

     INSERT INTO [ServerLoginHistory]

          SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()

    END

END –tr_ServerLoginHistory

GO


ENABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER

GO


–DISABLE TRIGGER [tr_ServerLoginHistory] ON ALL SERVER

–GO

Posted on by — Leave a comment

apache2

Notes for managing vhosts on apache2 (Apache 2.2.16 Ubuntu)

Add new vhosts:
1. create folder for site in /var/www/vhosts/
2. create vhost file in /etc/apache2/sites-available
3. create ln to sites-available file from sites-enabled
4. Restart Apache
/etc/init.d/apache2 restart

To check version:

apache2ctl -v or apache2 -v 

Posted on by — Leave a comment

Steps to clean install new Mule ESB server version

  1. unregister old version in MMC
  2. mule stop
  3. mule remove
  4. archive old version folder
  5. remove old version folder
  6. unzip new version of mule
  7. Change %MULE_HOME% environmental variable to point to new version
  8. start new command shell
  9. check echo %MULE_HOME% to confirm value set to new version
  10. mule -installLicense licensefile.lic
  11. mule install
  12. mule start
  13. register new version in MMC
  14. deploy mule apps

Posted on by — Leave a comment

Find OS User for command shell spawn from SQL Server context

If the policy on the server allows execution of xp_cmdshell then can find out the windows user that owns the spawned shell to handle OS commands executed from within sql server context

   EXEC xp_cmdshell 'whoami'

to get the value into a variable can do following

   DECLARE @whoisit nvarchar(4000)
   CREATE TABLE #test ( output nvarchar(4000) null )

   INSERT INTO #test (output)
   EXEC xp_cmdshell 'whoami'

   SELECT top 1 @whoisit = output 
     FROM #test 
    WHERE output IS NOT NULL

   SELECT @whoisit "whoisit"

   DROP TABLE #test