Using MS SQL Server

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Using MS SQL Server

Post by davidjman238 » 2024-04-02 21:20

I cannot get hMS 7 to install with MS SQL Server 2019. I have tried all kinds of permutations of permissions and accounts (Windows Auth, SQL Server Auth, different accounts, etc.), and I either get "not allowed on database 'master'" or "large OLE DB operation failed."

...Cryptic messages that don't actually tell me what I've done wrong. I feel like this should be simple.

Does installing with an MSSQLSERVER database even work?

User avatar
RvdH
Senior user
Senior user
Posts: 3249
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Using MS SQL Server

Post by RvdH » 2024-04-02 22:10

Maybe you should ask the developer of HMS 7, no such version here :lol:
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Re: Using MS SQL Server

Post by davidjman238 » 2024-04-02 22:12

Pardon my typo. Five-POINT-seven.

My question still stands.

User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Re: Using MS SQL Server

Post by davidjman238 » 2024-04-02 22:32

Using Windows Auth:

Code: Select all

Starting task...
Please wait while creating database...
Source: ADOConnection::Execute(), Code: HM10044, Description: Error while executing SQL statement: 
create database HMSDB
Microsoft OLE DB Driver for SQL Server
CREATE DATABASE permission denied in database 'master'.

User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Re: Using MS SQL Server

Post by davidjman238 » 2024-04-02 22:39

So I finally got it to work, but the solution... troubles me.

I had to create a new SQL Server account and grant it SysAdmin permissions.

Is this level of permission really necessary to run going forward?

User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Re: Using MS SQL Server

Post by davidjman238 » 2024-04-02 23:25

Ok, never mind. Not going to get any direction here I guess. Reverted my VMWare Snapshot and did the whole thing over. Created a new SQL Server account with the server-wide dbcreator role, let the wizard create the db with that account, then removed the server-wide role from the acount. Hopefully this was the correct way to do it.

User avatar
RvdH
Senior user
Senior user
Posts: 3249
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Using MS SQL Server

Post by RvdH » 2024-04-02 23:33

Think most users here use mysql/mariadb or built-in mssql ce
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
mattg
Moderator
Moderator
Posts: 22444
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Using MS SQL Server

Post by mattg » 2024-04-03 09:32

RvdH wrote:
2024-04-02 23:33
Think most users here use mysql/mariadb or built-in mssql ce
Yep,
davidjman238 wrote:
2024-04-02 23:25
Ok, never mind. Not going to get any direction here I guess. Reverted my VMWare Snapshot and did the whole thing over. Created a new SQL Server account with the server-wide dbcreator role, let the wizard create the db with that account, then removed the server-wide role from the acount. Hopefully this was the correct way to do it.
You shouldn't need to create a new SQL Server user

these permissions are required
viewtopic.php?t=20778#p126633
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Re: Using MS SQL Server

Post by davidjman238 » 2024-04-03 23:51

mattg wrote:
2024-04-03 09:32
You shouldn't need to create a new SQL Server user

these permissions are required
viewtopic.php?t=20778#p126633
As I said before, using Windows Auth (with my account that can create DB's in SQL Server just fine) does *not* work. That means I have to specify a SQL Server account instead. Since I am not going to operate the email server with sa, the built in Server Administrator account, that means I have to create an account for hMS. Even the post you linked references an account made specifically for hMS and asks about what permissions it needs. This is database 101 — you always make a database account per application, instead of giving global permissions to a single application.

To all appearances, the DB Setup is broken / programmed incorrectly. It should not need database-creation privileges, it should only need access to its own database, as created in advance by the user (which I tried, and which does not work — the DB Setup insists on trying to create the database, even if it already exists).

User avatar
mattg
Moderator
Moderator
Posts: 22444
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Using MS SQL Server

Post by mattg » 2024-04-04 10:23

Just so that you know
https://www.hmailserver.com/state
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
davidjman238
New user
New user
Posts: 16
Joined: 2023-01-06 09:03

Re: Using MS SQL Server

Post by davidjman238 » 2024-04-15 06:06

mattg wrote:
2024-04-04 10:23
Just so that you know
https://www.hmailserver.com/state
Yes, I am aware of this. However, the other alternative I found, MailEnable, appears to be a nightmare to manage (and costs a small fortune for a one-man operation). So for the moment I'm trying to see if I can make this application work.

I do run Virtualmin (with Postfix+Dovecot) on another server, but it's slow as molasses on mail receipt/delivery and I don't know why. Also, since I'm a vb.net developer, I'd prefer to keep things in the Windows domain if I can. Linux is only "free" if your free time means nothing at all to you.

Post Reply