Connecting to SQL Server 2008 External

[jnpatrick]

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]

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

[jnpatrick]

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.

[jnpatrick]

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]

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.

[jnpatrick]

Can you give us more information?

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

[rlevis]

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.

[jnpatrick]

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]

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.

[mattg]

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'?

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?

[rlevis]

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.

[mattg]

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...

[rlevis]

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?

[mattg]

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.)

[rlevis]

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]

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

[rlevis]

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

[Jay1b]

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]

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]

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...