User Permissions MSSQL 2005.

[blakey2]

Hi All,

Newbie question here so please bear with me.

We use GFI for our Anti-Spam and have been logging email to an Access/MSDE db for some time successfully.

Now I want to move to a full blown SQL db, so I can have a db size greater than 2GB.

[] I have installed MS SQL Server 2005.
[] I have installed all management tools etc.
[] Installation went fine.
[] SP1 has been applied via WSUS.

When I go into GFI I should be able to use it to create the database by specifying the server, user credentials and password.

The server I can select from a drop down box ie: myserver\MAILARCHIVEDB

When I enter user credentials I get an error in GFI, in the windows Event Logs and in the SQL event logs.

Essentially it is an error:
Login failed for "username". The user does not have a trusted SQL connection to the database. [CLIENT: 192.168.20.31]

I looked in SQL (enterprise?) management console and it seemed that the 'sa' account was simply an alias for my domain admin account.

I tried using both of these credentials: 'sa' and 'MYDOMAIN\administrator'. - Neither worked.

Additionally when I installed SQL it asked if I wanted to install using an integrated windows account (something like that) and I clicked yes, created a user 'sql.mailadmin' in AD and installed under those credentials.

All of the services start and run using 'sql.mailadmin' user and credentials.

I tried creating the account using sql.mailadmin's credentials but they fail with the same error.

I tried creating specific SQL users from the admin console with full rights/permissions over everything but they too fail.

It is possible that GFI needs some specific configuration, but thought I might ask if I have missed something obvious on the SQL side of things??

Cheers.

 

[mrdenny]

You need to setup the SQL Server for mixed mode authentication.

In the GUI, connect to the SQL Server in the Objhect Explorer. Right click on the server, properties. Security section. Sever Authentication section on the right. Select the second option and restart the SQL Service. It should now let you in using the SQL Account.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[blakey2]

Hi mrdenny,

Thanks for the advice. I have done so and am still having difficulties.

I have done some further research on GFI kb. The following link suggests what you mentioned and a bit more:

http://support.gfi.com/manuals/en/marktest/mar3manual.1.11.html

I have tried all this and is still not working.

I have posted a question in the GFI forum as i believe my issue probably lies with GFI.
If you are interested:

http://forums.gfi.com/SQL_Server_2005_and_ME_12_Mail_Archiving/m_900738623/tm.htm

Quick question though - What is the password for the 'SA' account. Looking at the SQL GUI, I thought it was an alias/shortcut to my DOMAIN\administrator account?? Would this be correct, or would it be an alias for the 'sql.mailadmin' user which SQL runs under?

Thanks - blakey2!

 

[mrdenny]

sa is a seperate account. The password would be what ever you set it to when you first installed SQL. If you don't know the password you should set it to a strong password and change it every once in a while.

Also make sure that no one becides the DBAs has access to the sa password.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[blakey2]

Hi All,

After ignoring my problem for a while I got around to having a go at fixing it.

I ended up creating a new account (Username 'IT' with a strong password) which relied upon SQL authentication, and gave it database admin rights over a database that I created for the mail archiving. 'GFIMailArchive'

When I put these credentials into GFI, it accepted them and is now happily archiving mail to my SQL db

Note: it would not accept the SA account, and gave some ODBC error about permissions required to create databases or something along those lines...

Cheers.

Thanks Mr Denny for input.