Connecting to SQL Server 2008 External

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
jnpatrick
New user
New user
Posts: 18
Joined: 2010-06-09 09:51

Connecting to SQL Server 2008 External

Post by jnpatrick » 2010-09-11 22:07

I feel like a newb today.

I'm installing hMailserver latest stable on Server 2008 R2, with a SQL 2008 Express R2 backend on the same machine.

When attempting to create a database all i get is;

Starting task...
Please wait while creating database...
ADO: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I'm sure i'm doing something wrong in the settings. I can't seem to find any documentation as to what to do here. I've been guessing but nothing is working for me.
Database Server Address - 27.0.0.1
Port - Greyed Out
Database Name - hMailserver (it's not clear whether i need to create this DB first or not)
Using SQL Auth - I created an account and made it a sysadmin (then tried all permissions) I also enabled the SA acocunt and tried that. I have tried Windows Auth as well. I did set the SQL server to SQL Auth

What am I missing?

bescher
Normal user
Normal user
Posts: 123
Joined: 2008-05-26 01:56
Location: Milwaukee Wi
Contact:

Re: Connecting to SQL Server 2008 External

Post by bescher » 2010-09-12 08:59

Be like me and do a duh.
Did you try using localhost under machine name rather thjan the actual name or ip address?

When Martin suggested that it connected right away after me trying the other 2 for 2 hours

User avatar
jnpatrick
New user
New user
Posts: 18
Joined: 2010-06-09 09:51

Re: Connecting to SQL Server 2008 External

Post by jnpatrick » 2010-09-12 10:40

Is there anything else that needs to be started? Like SQL Agent or SQL Browser? Are there any other pieces that need to be installed?

I tried the localhost with no luck. If I look in the SQL logs i don't even see any sort of login failure message.

For giggles I tried on another machine using SQL 2005 Express this time. Again on 2008r2 (64) and used the x64 version of sql express 2005. I tried with and without creating the database beforehand.

I am thinking my error is the database name field. Right now i'm siply putting in "hMailServer", is that correct or is it looking for a server name as part of that as well?

I know this is something simply wrong but i do now know what.

User avatar
jnpatrick
New user
New user
Posts: 18
Joined: 2010-06-09 09:51

Re: Connecting to SQL Server 2008 External

Post by jnpatrick » 2010-09-12 10:57

So I solved this and am posting here for future searches.

When using SQL Express you mostly will need to specify the instance name in all connections.
So when using the database address field you must specify computername\instancename

So where I was using localhost, the correct way would be localhost\SQLExpress (the default sql 2005 instance name)
In SQL 2008 Express you can specify an instance name during the install so it would be whatever you chose, however SQLEpxress is still the default.

And for the record, the database does not need to be creted beforehand, the tool does this for you.

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2010-10-09 10:07

I'm trying to do this as well and not having any luck. I thought hmail may as well use the SQL Server 2008 already installed on this Windows Server 2008 PC.

I'm getting the "SQL Server does not exist or access denied" message.

I didn't install the SQL server. The MS-SQL Server Management Studio connects fine and says the server name is something like CL-T123-456CN, which is the Windows name given to the computer.

I've tried many combinations in the Database Server Address including...
CL-T123-456CN
localhost
localhost\CL-T123-456CN
localhost\MSSQLSERVER
localhost\SPLEXPRESS

I'm using Windows Authentication.

Is "hMailServer" OK in the Database Name field?

Any further ideas?

Thanks.

User avatar
jnpatrick
New user
New user
Posts: 18
Joined: 2010-06-09 09:51

Re: Connecting to SQL Server 2008 External

Post by jnpatrick » 2010-10-09 14:43

Can you give us more information?

What version of SQL are you using. What is your SQL instance name?

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2010-10-09 15:13

It is Microsoft SQL Server Express Edition with Advanced Services v10.0.2531.

Where do I find the instance name? In the Management Studio / Server Properties, it says the Name is CL-T123-456CN (not quoted exactly).

Thanks.

User avatar
jnpatrick
New user
New user
Posts: 18
Joined: 2010-06-09 09:51

Re: Connecting to SQL Server 2008 External

Post by jnpatrick » 2010-10-09 15:29

Assuming you didn't change the instance name of the SQL server during the install it would be;

localhost\SQLEXPRESS or ComputerName\SQLExpress

If you did choose a different name then it would be;

localhost\InstanceName or ComputerName\InstanceName

The database name is just what you want the database to be called. Don't create it beforehand.

If you don't know your instance name, then you'll have to google a bit. I'm not 100% sure how to find that. Though if you open up your managment studio and connect you should see what it is.

Edit - Using Localhost of course assumes hMailServer and SQL server are on the same box.

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2010-10-10 00:56

Everything is on the same box.

I didn't install the SQL Server and don't know what was entered. I googled for determining the instance name and I didn't find a precise description for SQL 2008, but I found an Instance ID in the Configuration Manager > SQL Server Services > SQL Server. On the Advanced tab I see an Instance ID of MSSQL10.MSSQLSERVER.

I tried entering that with no luck. I also tried...
localhost\MSSQL10.MSSQLSERVER
CL-T123-456CN\MSSQL10.MSSQLSERVER

I think I will have to give up and use the hMailServer's own database system.

Edit: I just tried disabling Shared Memory and enabled TCP access to the server, but the hMail database setup still doesn't ask for the Port number. I find that odd.

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

Re: Connecting to SQL Server 2008 External

Post by mattg » 2010-10-10 09:52

rlevis wrote:I didn't install the SQL Server and don't know what was entered.
Are you sure that it has been installed?
Is there a SQL Service manager installed?
What SQL Services are listed in 'services'?
rlevis wrote:Edit: I just tried disabling Shared Memory and enabled TCP access to the server, but the hMail database setup still doesn't ask for the Port number. I find that odd.
You know that MS SQL server doesn't allow you to set ports right?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2010-10-10 11:00

Of course it is installed or or I wouldn't have been quoting settings from the SQL Server Configuration Manager and the SQL Server Management Studio which connects fine to the SQL server.

Services running are.
SQL Server (MSSQLSERVER)
SQL Server Reporting Services

SQL Server Browser is stopped
SQL Server Agent is stopped
You know that MS SQL server doesn't allow you to set ports right?
I can see it set in the Config Manager to 1433 and it does let me modify it.

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

Re: Connecting to SQL Server 2008 External

Post by mattg » 2010-10-12 04:49

Without trying to annoy you...(I really am just offering free assistance)

If your computer name is CL-T123-456CN and your instance name is MSSQLSERVER then your connection string will be CL-T123-456CN\MSSQLSERVER

The ports for MSSQL are different for each installation, they are dynamically set. The main reason to change port numbers is to avoid known conflicts. The SQLServer will be contacted via named pipes, not via ports... MSSQL is quite different than MySQL in it's behaviour. Also the SQL Manager and Management Studio can be deployed across a network to a remote SQL Server, however because you have a SQLServer service running then it is installed on that computer.


You know that you could choose a MySQL database if that is what you are more comfortable with...
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2010-10-12 05:52

Thanks for your reply, but I did mention I tried
CL-T123-456CN\MSSQLSERVER
and several others.

Named pipes are disabled in the configuration. The only option enabled in the SQL server is Shared Memory. 3 other options may be enabled or disabled - Named Pipes, TCP/IP, VIA. I've just enabled Named Pipes.

I'm not sure if this changed the error or if I didn't notice it had changed earlier, but now I get this error which looks like some progress.

ADO: [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection.

Any ideas?

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

Re: Connecting to SQL Server 2008 External

Post by mattg » 2010-10-12 06:39

rlevis wrote:Thanks for your reply, but I did mention I tried
CL-T123-456CN\MSSQLSERVER
and several others.
I didn't and still can't see that exact one...I even just searched the page without finding it...

Have you tried restarting the server since you changed to named pipes. At least restarting the MS SQL service might help...
The other thing is to ensure that you have valid user access and passwords...

(I use MSSQL quite a bit, but not with hmailserver. I use MySQL for hMailserver.)
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2010-10-12 08:38

SQL Server warns that the service needs restarting to take effect, and I did do that.

The SQL Server config says it is set to Windows Authentication, and that is what I have hMailServer set to. I can change to Server authentication which I will try later.

Jay1b
New user
New user
Posts: 18
Joined: 2011-01-14 14:23

Re: Connecting to SQL Server 2008 External

Post by Jay1b » 2011-02-05 12:13

jnpatrick. Thank you for posting the solution! Much apprecriated.....

rlevis
Normal user
Normal user
Posts: 35
Joined: 2010-10-09 09:55

Re: Connecting to SQL Server 2008 External

Post by rlevis » 2011-02-05 15:09

what solution is that? I think you posted to the wrong thread.

Jay1b
New user
New user
Posts: 18
Joined: 2011-01-14 14:23

Re: Connecting to SQL Server 2008 External

Post by Jay1b » 2011-02-06 23:59

rlevis wrote:what solution is that? I think you posted to the wrong thread.
Assuming you didn't change the instance name of the SQL server during the install it would be;

localhost\SQLEXPRESS or ComputerName\SQLExpress


That one :)

mihalko
New user
New user
Posts: 1
Joined: 2011-06-20 07:43

Re: Connecting to SQL Server 2008 External

Post by mihalko » 2011-06-20 07:58

In Microsoft SQL Server Management Studio right click on the server in the Object Explorer frame - Server Properties window will come up. In the General tab it will give you the name of the server and instance.

That server and instance name is all you need, no localhost or 127.0.0.1 needed. All I typed in was the server and instance name and it accessed Microsoft SQL Server 2008 R2 just fine.

Hope this helps,

cyber_nicco
New user
New user
Posts: 1
Joined: 2012-11-08 18:23

Re: Connecting to SQL Server 2008 External

Post by cyber_nicco » 2012-11-08 18:30

Hello Everyone,

I've been going through this thread (and a similar one on this forum) trying each solution as I've had the same problem. What I've found is that one needs to enable TCP/IP connection in the SQL Configuration Manager (and restart SQL Server Services) to connect. I had undone all the other suggestions each time I did this (such as adding the sql instance name to the server name field, and enabling named pipes on SQL Server) and this one change is what resolved it (for me, at least).

I hope that helps at least one other person...

Post Reply