Grails 2.0 SQL Server Configuration

I just created my first test application with Grails 2.0RC1 and used SQL Server 2008 as the database.

Here are the quick steps:

1. Install JDK/JRE (I used 1.6.latest)
2. Unzip Grails 2.0RC1 to an C:\grails2rc1\ directory
3. Create environment variables for JAVA_HOME(point to jdk install)
4. Create environment variables for GRAILS_HOME(point to grails directory)
5. Add the following to PATH environment variable: ";%JAVA_HOME%\bin;%GRAILS_HOME%\bin"
6. Test that you can call "javac" and "grails --help" from C:\

7. Create a database for grails from Management Studio
8. Create a user/pass and give it access to this database(I usually give it dbo and sysadmin during the initial install phase. You should dial it back after that)
9. ** TRICK ** Make sure TCP/IP is ENABLED using SQL Server Configuration Manager, under SQL Server Network Configuration -> Protocols for MSSQLSERVER

10. Create a directory for your app (C:\work\apps)
11. Open a command prompt and "cd" to that directory
12. "grails create-app myapp" (This creates the Grails structure)

13. Download the latest jTDS Driver and extract the files to a temp directory
14. Copy the jtds-1.2.x.jar file to the directory C:\mayapp\lib
15. Your conf/DataSource.groovy should look something like this:
dataSource {
pooled = true
driverClassName = "net.sourceforge.jtds.jdbc.Driver"
dialect = "org.hibernate.dialect.SQLServerDialect"
}
... other stuff
development {
dataSource {
dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
url = "jdbc:jtds:sqlserver://127.0.0.1:1433;databaseName=grails2rc1"
username = "grailsadmin"
password = "your-Pass123"
// logSql=true
}
}
... more

16. Create your 1st controller: "grails create-controller dashboard"
17. Save, then run "grails run-app"

Backing Up SQL Database to Network Drive - Operating system error 5(Access is denied)

I ran into this issue the other day while trying to run a backup and store it on a network drive. At first, I thought it was a file permissions issue so I checked in the Security Settings and Audit logs, but no matter what permissions I changed, it still did not work.

The issue ended up being that the account that the SQL Server service runs under needs to be a Domain account and have the proper permissions. There was also a few other configuration issues I stumbled across.

1. Local Security Policy: Under the Local Security Policy(or Group Policy), there is a policy called "Lock pages in memory" and this needs to be enabled for the domain service account that will be used for the SQL Server service (I also changed the SQL Agent service).

2. The next steps were to allow us to reconfigure SQL Server and allow the SQL Agent to run as domain account:

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'awe enabled', 1

RECONFIGURE;

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Agent XPs', 1;

RECONFIGURE

sp_configure 'show advanced options', 0;

RECONFIGURE

3. Change your SQL Server Service and SQL Agent service Logon account to a domain account with privileges on the network drive.
Open Services -> Choose the Service -> Logon tab -> Choose "This Account" and enter DOMAIN\ACCT and enter the password.

Scheduled Task Not Running a Batch Job

I've recently ran into this several times, so I'm thought I'd document it here.

I've created batch jobs (.bat) that do various things, like copy, move, delete files, download stuff from SFTP servers, etc. In the past, I've just used my credentials to run these jobs, but if I changed my password, the jobs would fail. Now, our IT department created a service account for me to Schedule these tasks, but I've had many issues with Scheduled Tasks not working once I changed the credentials to the service account.

The scheduled task will not show an error code, it just won't work or it will be stuck in a "running" state.

Here is what I have found out:

1. The service account should be a local admin
2. Delete your old Scheduled Task
3. Log onto the server using the service account credentials
4. Re-Create the Scheduled Task while logged on as the service account and use the service account credentials

This applies to Scheduled Tasks that run a .BAT file. All my other scheduled tasks work normally.

Migrating users to DotNetNuke

I've been on a 2 month hiatus, but I've come back with some new skills.  I've been working on a DotNetNuke portal to replace our existing MojoPortal.  I really wanted to like MojoPortal because of it's simplicity and ease of setup, but in the end MojoPortal Core development pretty much came to a halt.  There are just too many standard features that are missing, plus DNN has so much out of the box, I couldn't resist.  Plus, the community behind DNN is huge.

While browsing Mitchel Sellers' blog, I found some SQL that allows me to migrate the 700+ user accounts in our MojoPortal to DotNetNuke.  I've put it in a script and parametized it.

After I created this, I created a table from my old MojoPortal, which contains: UserName, FirstName, LastName, DisplayName, Email, and RoleName (Primary Role, which must exist in DNN before running the script).  Then, I ran it through a cursor and added all the users.

** ADDITION NOTE:  If you are using {owner}{prefix} in DNN, you'll need to modify the script.

-- =======================================================================================================

-- Author:        Larry Eisenstein

-- Create date: 2/15/2011

-- Description:    Creates a DNN User by copying an existing user.

-- This can be used to script a single account creation or migrating users from another system.

-- This works by creating the NewUser from an existing user.

-- The script was pulled from Mitchel Sellers website.  He has a great blog, so you should visit it.

-- http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/84/creating-a-standard-dotnetnuke-user-via-sql.aspx

-- 

-- 

-- Req: 

-- 1. Know the Username/Password of an existing user. The password for the user you create will be the password 

-- of this user

-- 2. If you are assigning Roles, the RoleName MUST existing in the DotNetNuke site

-- 

-- Defaults:  I set up some defaults, but these can be changed via params or just change the defaults in the script.

-- 

-- Notes: 

-- Stored Procs used: aspnet_Membership_CreateUser

-- Tables Updated: users, Roles, UserPortals

-- The password works by copying the encrypted password, passwordsalt of the ExistingUser to your new user.  Then, you can

-- just login with that user's password.

-- ==========================================================================================================

CREATE PROCEDURE [dbo].[AAA_MigrateUser_sp]

@ApplicationName varchar(255) = 'DotNetNuke',  -- Search for applicationName in your web.config

@ExistingUserName varchar(255) = 'TestUser',   -- This must be an existing DNN user

@FirstName varchar(255) = 'Migrated',            

@LastName varchar(255) = 'UserAccount',

@DisplayName varchar(255) = 'Migrated UserAccount',

@NewUserName varchar(255),

@RoleName varchar(255) = 'Registered Users',

@PortalId int = 0,

@Email nvarchar(256) = 'TestUser@email.org'

AS

BEGIN

DECLARE @PasswordQuestion varchar(256),

@PasswordAnswer varchar(256),

@Pw varchar(255),

@PasswordSalt varchar(255),

@PasswordFormat int,

@IsApproved bit,

@CurrentTimeUtc datetime,

@CreateDate datetime,

@UniqueEmail int,

@UserId uniqueidentifier,

@DNNUserId int,

@NumUsers int

SELECT    @PasswordQuestion = '',

@PasswordAnswer = '',

@IsApproved = 1,

@CurrentTimeUtc = GETDATE(),

@CreateDate = @CurrentTimeUtc,

@UniqueEmail = 0

SELECT @NumUsers = COUNT(*)

FROM aspnet_Users

WHERE UserName = @NewUserName

IF(@NumUsers != 0)

return -1

SELECT    @Pw = m.password,

@PasswordSalt = m.passwordsalt,

@PasswordFormat = m.passwordformat

FROM aspnet_users u

INNER JOIN aspnet_membership m  ON (u.userid = m.userid)

WHERE u.UserName = @ExistingUserName

-- Make the stored procedure call

EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @NewUserName, @Pw,

@PasswordSalt, @email, @passwordquestion, @PasswordAnswer, 

@IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,

@PasswordFormat, @UserId

-- Insert the record into the DotNetNuke users table

INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,

DisplayName, UpdatePassword)

VALUES(@NewUserName, @FirstName, @LastName, 0, @Email, @DisplayName, 0)

-- Get the new userid, from the DNN users table

SELECT @dnnuserid = userid

FROM Users

WHERE username = @NewUserName

-- Now, insert the record into the user portals table

INSERT INTO UserPortals (userId, PortalId, CreatedDate)

VALUES(@dnnuserid, @PortalId, GETDATE()) 

-- Now Give the user permissions to the User Group you specified

IF(@RoleName != 'Registered Users' and @RoleName IS NOT NULL)

BEGIN

INSERT INTO UserRoles (userId, roleId)

SELECT @dnnuserid,

roleId

FROM Roles

WHERE RoleName = @RoleName

END

-- Now Give the user permissions to the REGISTERED Users group

INSERT INTO UserRoles (userId, roleId)

SELECT @dnnuserid,

roleId

FROM Roles

WHERE RoleName = 'Registered Users'

END