Configuring a new user for SQL server
Ideally, we'll create a new user for SQL server through which the Prism will load data to. To create a new user in SQL Server:
- If this is an upgrade or first time installation, then you'll need to restore the prism DB. This is done by right clicking 'DataBase'-> 'Restore DataBase'
You'll be prompted with:
- Click 'device', then the three dots '…' and select 'Add'
- Expand the path detailed in the screen shot above, and select the relevant DB file according to the instance on the machine, this will be a 'prism.bak' file.
Then in the previous screen shot, you'll need to set the DB to restore to, also according to the Server instance type. (i.e. 2008 R2= 'Prism' , 2008 = 'Prism_2008')
Then when setting the 'user mappings' in future steps, you'll select the user to work against this selected DB.
- In the object explorer, right click 'Security'-> 'New' -> 'Login'
- Enter all desired user credentials: best Login Name: prism/prismWeb or such. Make sure you check 'SQL Server authentication', and uncheck 'Enforce password policy' and the other two checkboxes should also uncheck themselves.
- In 'User Mapping', 1. check 'Prism', 2. in the checklist underneath check 'db_owner', 3. OK
- Go back to the Object Explorer, right click the new Login you created, 'Properties'
- Under 'User Mapping', there should be 'dbo' under 'Default Schema', in most cases you'll have to recheck 'db_owner'.
- 'OK', now the new user is created.
- Back to the 'Object Explorer', make sure your new Login exists under the 'Prism' DB.
- We'll have to reconfigure the db.config file's connection string:
- Make sure the correct provider name is placed.
- Make the following changes in the respective DataProviderName (see below):
- 'Data Source' = the DB instance. To find this instance name, right click the top most general DB, right click -> 'properties', and take the 'Name' field.
- If need be, add 'user'=Login name you created
- If need be, add 'Password'= Login's password you created
- Change 'Integrated Security' to False.
- SAVE db.config file!!
- Try browsing to the localhost or Prism web publish location. If you don't manage to browse, don't hesitate and in the cmd prompt, 'iisreset'.