EEEExternal User configuration - Revision history

Trevor at 04:25, 14 January 2011

2011-01-14T04:25:31Z

← Older revision		Revision as of 04:25, 14 January 2011
Line 1:		Line 1:
	Created: 17 November 2009. Last Reviewed: ~~17 November 2009~~		Created: 17 November 2009. Last Reviewed: 14 January 2011


Line 16:		Line 16:
	Manual steps required:		Manual steps required:

	# Create a Windows Group (e.g. call it EEEExternalUsers) note this is has an 's' on the end so it does not clash with ‘EEEExternalUser’ for the SQL Server authentication (see below)		# 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)
	# Add required people's Windows accounts into this Windows Group		# Add required people's Windows accounts into this Windows Group
	# In SQL Server (Enterprise Manager) create a new login for the Windows Group		# In SQL Server (Enterprise Manager) create a new login for the Windows Group

Trevor at 05:06, 17 November 2009

2009-11-17T05:06:26Z

← Older revision		Revision as of 05:06, 17 November 2009
Line 12:		Line 12:
	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.		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 ~~Product Insert Excel~~ files.		EEE has a database role called “EEEExternal” which must be used to give permission to users of the EEE spreadsheet files.

	Manual steps required:		Manual steps required:
Line 23:		Line 23:

	=== SQL Server Authentication ===		=== SQL Server Authentication ===
	A SQL Server login called '''EEEExternalUser''' must be created. This same login will be used by everyone using the EEE ~~Product Insert Excel~~ files.		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.		EEE has a database role called “EEEExternal” which must be used to give permissions to EEEExternalUser.
Line 33:		Line 33:
	# Give it a password		# Give it a password
	# 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)		# 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)
	# People will be prompted for the password for the EEEExternalUser when they use ~~CTRL+SHIFT+J~~ 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.		# 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.

Trevor: New page: Created: 17 November 2009. Last Reviewed: 17 November 2009 '''EEEExternal User configuration''' When using EEE spreadsheets to interface with EEE, the spreadsheets access the EEE databa...

2009-11-17T05:04:11Z

New page: Created: 17 November 2009. Last Reviewed: 17 November 2009 '''EEEExternal User configuration''' When using EEE spreadsheets to interface with EEE, the spreadsheets access the EEE databa...

New page

Created: 17 November 2009. Last Reviewed: 17 November 2009

'''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 Product Insert Excel files.

Manual steps required:

# Create a Windows Group (e.g. call it EEEExternalUsers) note this is has an 's' on the end so it does not clash with ‘EEEExternalUser’ for the SQL Server authentication (see below)
# Add required people's Windows accounts into this Windows Group
# In SQL Server (Enterprise Manager) create a new login for the Windows Group
# 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)
# 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 Product Insert Excel files.

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

Manual steps required:

# 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)
# Create a new SQL Server login. This MUST be called '''EEEExternalUser'''
# Give it a password
# 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)
# People will be prompted for the password for the EEEExternalUser when they use CTRL+SHIFT+J 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.