Author: sqldba

REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REM
REM Use this batch file to start IIS web sites when the server is started. To use, ensure that all following steps have been followed.
REM 1. Place this as a batch file on the server ( C:StartWebsites.bat )
REM 2. Edit the startup group policy setting for the local machine
REM PATH : ( Group Policy Object Editor – Local ComputerPolicy – Computer Configuration – Windows Settings – Scripts – Startup)
REM 3. Add a script with the path to the batch file as the script name ( Script Name: C:StartWebsites.bat )
REM
REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REM CScript Host is required to use IIS command line tools
cscript.exe //H:CScript

REM Start web sites

REM web sites
iisweb /start wiki
iisweb /start development

REM Reset default scripting host to WScript in case anything else depends on it.
cscript.exe //H:WScript

Pass this on to your folks if they get the following error in a asp.net app on the development server:

“This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms”

Have your developer go into the web config and move the “sessionState” tag to just under the tag and make sure the sessionState is uncommented.

To this point I still only have speculation as to what changed on the server that now is causing this error. Most likely was a setting that changed as part of a security update.

‘cscript setmetabase.vbs

‘set vdirObj=GetObject(“IIS://localhost/W3svc/1/ROOT”)
‘set vdirObj=GetObject(“IIS://localhost/W3SVC/1/Root/rapid/projects/proj1”)
‘set vdirObj=GetObject(“IIS://localhost/W3SVC/1/Root/rapid/projects/proj_Prototype”)
set vdirObj=GetObject(“IIS://localhost/W3SVC/1/Root/rapid/projects/proj2”)

‘ Print out the current value of some properties:
WScript.Echo “UploadReadAheadSize Before: ” & vdirObj.UploadReadAheadSize

‘ Set some properties:
‘ default is 49152
‘vdirObj.Put “UploadReadAheadSize”, 1024000

‘ Save the property changes in the metabase:
‘vdirObj.SetInfo
‘WScript.Echo “UploadReadAheadSize After: ” & vdirObj.UploadReadAheadSize

If you get a message ‘security log is full’, can take the following steps….

1. log in as administrator

2. right-click ‘my computer’ icon and select ‘manage’

3. in the computer management window, expand ‘system tools’, then expand ‘event viewer’

4. select the ‘security’ entry

5. from the computer management window menu, select ‘action’, then ‘clear all events’

6. click ‘yes’ to save a copy of the log, or ‘no’ to clear the log completely

USE model
GO

———————————
— ap_developer
———————————

DECLARE @RoleName sysname
set @RoleName = N’ap_developer’
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = ‘R’)
Begin
DECLARE @RoleMemberName sysname
DECLARE Member_Cursor CURSOR FOR
select [name]
from dbo.sysusers
where uid in (
select member_principal_id
from sys.database_role_members
where role_principal_id in (
select principal_id
FROM sys.database_principals where [name] = @RoleName AND type = ‘R’ ))
OPEN Member_Cursor;
FETCH NEXT FROM Member_Cursor
into @RoleMemberName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
FETCH NEXT FROM Member_Cursor
into @RoleMemberName
END;
CLOSE Member_Cursor;
DEALLOCATE Member_Cursor;
End
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’ap_developer’ AND type = ‘R’)
DROP ROLE [ap_developer]
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’ap_developer’ AND type = ‘A’)
DROP APPLICATION ROLE [ap_developer]
GO

CREATE ROLE [ap_developer] AUTHORIZATION [dbo]
GO

EXEC sp_addrolemember N’db_datareader’, N’ap_developer’
EXEC sp_addrolemember N’db_datawriter’, N’ap_developer’
GRANT CONTROL ON SCHEMA::[dbo] TO [ap_developer]
GRANT VIEW DEFINITION TO [ap_developer]
GRANT CREATE PROCEDURE TO [ap_developer]
GRANT EXECUTE TO [ap_developer]
GO

———————————
— ap_user
———————————
DECLARE @RoleName sysname
set @RoleName = N’ap_user’
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = ‘R’)
Begin
DECLARE @RoleMemberName sysname
DECLARE Member_Cursor CURSOR FOR
select [name]
from dbo.sysusers
where uid in (
select member_principal_id
from sys.database_role_members
where role_principal_id in (
select principal_id
FROM sys.database_principals where [name] = @RoleName AND type = ‘R’ ))
OPEN Member_Cursor;
FETCH NEXT FROM Member_Cursor
into @RoleMemberName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
FETCH NEXT FROM Member_Cursor
into @RoleMemberName
END;
CLOSE Member_Cursor;
DEALLOCATE Member_Cursor;
End
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’ap_user’ AND type = ‘R’)
DROP ROLE [ap_user]
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’ap_user’ AND type = ‘A’)
DROP APPLICATION ROLE [ap_user]
GO

CREATE ROLE [ap_user] AUTHORIZATION [dbo]
GO

EXEC sp_addrolemember N’db_datareader’, N’ap_user’
EXEC sp_addrolemember N’db_datawriter’, N’ap_user’
GRANT VIEW DEFINITION TO [ap_user]
GRANT EXECUTE TO [ap_user]
GO

/**********
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’jmolsen’)
DROP USER [jmolsen]
GO
CREATE USER [jmolsen] FOR LOGIN [jmolsen] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember N’ap_developer’, N’jmolsen’
GO
************/

From SQL Server 2005 Books Online:

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

USE AdventureWorks;
GO

WITH OrderedOrders AS
(
SELECT SalesOrderID
, OrderDate
, ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader
)

SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

/***********************************************
* This gives users a dummy default database
* in order to prevent them from:
* 1) having ‘master’ as a default database
* 2) not being able to connect to the server
* because their default database has been
* dropped, recreated, or restored
***********************************************/

USE master
GO

IF NOT EXISTS ( SELECT TOP 1 1 FROM sysdatabases WHERE name = ‘uno’ )
BEGIN
CREATE DATABASE [uno]
END
GO

ALTER DATABASE [uno] SET READ_WRITE
GO

USE uno
GO

GRANT SELECT TO [public]
GO

DECLARE @name sysname
, @sql nvarchar(max)

DECLARE login_cur CURSOR FAST_FORWARD FOR
SELECT name
FROM master..syslogins WITH (NOLOCK)
WHERE upper(name) LIKE ‘%’

OPEN login_cur

FETCH login_cur INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF NOT EXISTS ( SELECT TOP 1 1 FROM sysusers WHERE name = ”’ + @name + ”’ ) ‘
SET @sql = @sql + ‘CREATE USER [‘ + @name + ‘] FOR LOGIN [‘ + @name + ‘] WITH DEFAULT_SCHEMA=[dbo];’
EXEC sp_executesql @sql
SET @sql = ‘ALTER LOGIN [‘ + @name + ‘] WITH DEFAULT_DATABASE=[uno];’
EXEC sp_executesql @sql
FETCH login_cur INTO @name
END

CLOSE login_cur
DEALLOCATE login_cur
GO

–ALTER DATABASE [uno] SET READ_ONLY
–GO