EEEExternal User configuration

From EnablerWiki

Created: 17 November 2009. Last Reviewed: 14 January 2011

EEEExternal User configuration

When using EEE spreadsheets to interface with EEE, the spreadsheets access the EEE database using either an EEEExternal user login or a EEEExternalUsers group. Configuration of either authentication method is explained below.

Database and Security Set-Up

The EEE Excel files use either Windows Authentication or SQL Server Authentication to connect to SQL Server. This must be specified on the “Settings” worksheet. The default is Windows authentication.

Windows Authentication

This means that the Windows account of the person who is using the spreadsheet will need to have permission to log in to SQL Server to view and save data in the EEE database.

EEE has a database role called “EEEExternal” which must be used to give permission to users of the EEE spreadsheet files.

Manual steps required:

1. Create a Windows Group (e.g. call it EEEExternalUsers) note this has an 's' on the end so it does not clash with ‘EEEExternalUser’ for the SQL Server authentication (see below)
2. Add required people's Windows accounts into this Windows Group
3. In SQL Server (Enterprise Manager) create a new login for the Windows Group
4. Grant the new SQL Server login access to the EEE (EEETest) database and assign it to the EEEExternal database role (it will only need to be in public and EEEExternal roles)
5. People will need to log out and log in again for the changes to take effect.

SQL Server Authentication

A SQL Server login called EEEExternalUser must be created. This same login will be used by everyone using the EEE Spreadsheet files.

EEE has a database role called “EEEExternal” which must be used to give permissions to EEEExternalUser.

Manual steps required:

1. Make sure SQL Server is running in Mixed authentication mode SQL Server and Windows (if you need to make a change to set this, you need to stop and start SQL Server)
2. Create a new SQL Server login. This MUST be called EEEExternalUser
3. Give it a password
4. Grant the new SQL Server login access to the EEE (EEETest) database: assign it to the EEEExternal database role (it will only need to be in public and EEEExternal roles)
5. People will be prompted for the password for the EEEExternalUser when they use the specified keys to run the spreadsheet macros that access the database. Everyone who uses the spreadsheets will use this same login, so everyone will type in the same password.