SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 error while logging
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  13:50:15  Show Profile  Reply with Quote
Hi all,

I am getting the following error when I log into SQL server using MS SQL Server Management Studio.

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)".

I tried to allow remote connections using Surface Area Configuration, but it is not allowing me to do so.

I am not sure what I need to do. Anybody, please advice.

Thanks & Regards

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  14:00:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try connecting with ServerName,PortNumber.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/15/2008 :  14:00:56  Show Profile  Reply with Quote
What do you mean its not a;llowing you? What hepens when you change settings to allow remote connections?
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  14:12:23  Show Profile  Reply with Quote
Hi,

I have followed the following steps:

I clicked -- Start > All Programs > SQL Server 2005 > Configurations Tools > SQL Surface Area configuration > Surface Area Configuration for Services and Connections > Database Engine > Remote Connections
and selected the radio button Local and Remote Connections and under that I selected "Using both TCP/IP and named pipes" and clicked the apply button.

Once I clicked it, there was a message saying I need to restart the server. So I stopped the server and then started it again. When i started the server, it is giving me the following error message:

"TITLE: Surface Area Configuration
------------------------------

An error occured while performing this operation".

Can anybody please help?

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  14:14:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
I can help. Did you see my last post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  14:23:17  Show Profile  Reply with Quote
Thank you very much Tara.

How do I connect using Server name and Port number? I mean when I go to the SQL Server Management Studio Login window, It asks for server type, server name and Authentication type. So where do I give a port number?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  14:26:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
You would use that information instead of whatever you are typing in for the server name.

Here is what you should try for the server name:

ServerName,PortNumber
IPAddress,PortNumber
ServerName\InstanceName
IPAddress\InstanceName

Let us know which ones work and which ones don't. To see what port your instance is listening on, check SQL Server Configuration Manager.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  15:05:03  Show Profile  Reply with Quote
From where do I find out the following information:

ServerName,PortNumber
IPAddress,PortNumber
ServerName\InstanceName
IPAddress\InstanceName

I mean how do I check whether the information i am providing is correct or not?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  15:07:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
Go to a cmd window. Type hostname. Use that as ServerName. Now type ipconfig. Locate your IP address and use that for IPAddress. For PortNumber, check SQL Server Configuration Manager.

Did you install a default instance or a named one? If a default instance, then do not use the two where I have InstanceName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  15:31:07  Show Profile  Reply with Quote
Thanks for your help. I got the hostname, ip address and the port number.

I am not sure which instance I have installed. Can I know now which one I installed?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  15:34:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can easily tell this by looking at the name of the service or in SQL Server Configuration Manager. Does it say MSSQLSERVER in the name or something else?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  15:39:52  Show Profile  Reply with Quote
In the SQL Sever Configuration Manager, it says SQL Server(MSSQLSERVER).
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  15:41:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's the default instance then.

Try connecting in Management Studio with these:

ServerName
ServerName,PortNumber
IPAddress
IPAddress,PortNumber

Do any of these work?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  15:50:38  Show Profile  Reply with Quote

When I tried to connect in Management Studio with:

ServerName - it is giving the following error:
"TITLE: Connect to Server
------------------------------

Cannot connect to server name

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)



ServerName,PortNumber - it is giving the following error:
"TITLE: Connect to Server
------------------------------

Cannot connect to server name, port number.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)"



IPAddress - I could log on to the server.



IPAddress,PortNumber - I got the following error:
"TITLE: Connect to Server
------------------------------

Cannot connect to ip address, port number.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)"

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  15:58:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
Now try connecting with:

tcp:IPAddress,PortNumber
tcp:ServerName,PortNumber

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  16:02:53  Show Profile  Reply with Quote
tcp - is this just a key word? what I mean is should I try giving something like tcp:127.0.0.1, 1433?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  16:16:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
tcp is just telling it to connect with TCP/IP. Do not change that part.

Also, you should not be using 127.0.0.1 for the IPAddress. Use the other one found in ipconfig.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  16:27:21  Show Profile  Reply with Quote
When I tried to connet using tcp:IP Address, Port Number, it gave me the following error:

"TITLE: Connect to Server
------------------------------

Cannot connect to tcp:IP Address, Port Number.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)"




When I tried connecting using tcp: server name, port number - I got the following error:

"TITLE: Connect to Server
------------------------------

Cannot connect to tcp:server name, port number .

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)"




BTW, the TCP/IP protocol is in the disabled state in my server. Does this matter?

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  16:29:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes it matters. Enable it, restart SQL Server, and then try all of the suggestions again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ssss28
Starting Member

18 Posts

Posted - 04/15/2008 :  16:37:34  Show Profile  Reply with Quote
As I have already mentioned in my previous post, When I tried to enable the TCP/IP, I am stuck with the following:

I clicked -- Start > All Programs > SQL Server 2005 > Configurations Tools > SQL Surface Area configuration > Surface Area Configuration for Services and Connections > Database Engine > Remote Connections
and selected the radio button Local and Remote Connections and under that I selected "Using both TCP/IP and named pipes" and clicked the apply button.

Once I clicked it, there was a message saying I need to restart the server. So I stopped the server and then started it again. When i started the server, it is giving me the following error message:

"TITLE: Surface Area Configuration
------------------------------

An error occured while performing this operation".

Hope I have followed the steps correctly.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/15/2008 :  16:45:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
When it said to restart the server, did you restart the service or reboot? Please explain what you did here: "I stopped the server and then started it again".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000