How To use SQL Server 2005/2008

[MarcoM]

Using the LOGINventory Webinterface in conjunction with Microsoft SQL Server SQL Server Express 2005/2008.

After installing the Webinterface you maybe get an error message like
"LOGIN failed for user domain\user". Please ensure proper privileges for database access". This How To should help you to solve this issue.

General recommendation:
If you do not need to restrict database access for certain users, we recommend to use SQL-Authentication instead of NT-Authentication. This is as far as our experience goes the most uncomplicated way to configure and handle database access through the Webinterface.

There are two different authentication types accessing a SQL-Server databases. Here is how to configure them.

1. SQL-Authentication
Using Management Studio for SQL Server (Express) 2005/2008
• Open Management Studio Express and connect to your server instance. By default the server string is “yourserver\sqlexpress”.
• Expand the Security node of your server instance. Right click “Logins”, click “new Login”.
• Select the “SQL Server Authentication” radio button and type in a “Login name” (e.g. “loginventoryuser” and a password/confirm password
• Uncheck the fields “Enforce password policy”, “Enforce password expiration” and “User must change password at next login”.
• At “Default database” select your LOGINventory database.
• On the left hand side of this dialog select the page “User Mapping”.
• Map the user to your LOGINventory database by checking the “Map” checkbox of your Database.
• Check the “db_owner” “Database role membership for:” your LOGINventory Database.
• Click OK.

LOGINventory settings:
Open the LOGINventory Configuration Dialog, click “Select Database” and make sure the appropriate “Server” and “Database” is entered. Uncheck “Use NT Authentication”. As “UID:” use the username of the created user and type in the accordant password. Test your connection.

2. NT Authentication

This is usually unproblematic if the database server runs at the same server as LOGINventory does. If you run into authentication problems and you are not sure which user is finally connecting to the database (sometimes there are problems with delegation so that the user connecting to the db is a system account instead of the expected user account) check the SQL Server -> Management -> SQL Server Logs for access denied messages.

Using Management Studio Express for SQL Server (Express) 2005/2008
• Open Management Studio Express and connect to your server instance. By default the server string is “yourserver\sqlexpress”.
• Expand the Security node of your server instance. Right click “Logins”, click “new Login”.
• Into the „Login name:“ text field, type the Windows Group or the Windows User you want to grant access or select one through the search button.
• Select your LOGINventory database as the default database.
• On the left hand side of this dialog select the page “User Mapping”.
• Map the user to your LOGINventory database by checking the “Map” checkbox of your Database.
• Check the “db_owner” “Database role membership for:” your LOGINventory Database.
• Click OK.

You may download the Management Studio Express” from the Microsoft homepage