error while logging - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

error while logging

Author

Topic

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 13:50:15

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

38200 Posts

Posted - 2008-04-15 : 14:00:43

Try connecting with ServerName,PortNumber.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 14:00:56

What do you mean its not a;llowing you? What hepens when you change settings to allow remote connections?

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 14:12:23

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 14:14:00

I can help. Did you see my last post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 14:23:17

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 14:26:39

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 15:05:03

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 15:07:52

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 15:31:07

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 15:34:51

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 15:39:52

In the SQL Sever Configuration Manager, it says SQL Server(MSSQLSERVER).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 15:41:14

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 15:50:38

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 15:58:01

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 16:02:53

tcp - is this just a key word? what I mean is should I try giving something like tcp:127.0.0.1, 1433?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 16:16:40

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 16:27:21

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 16:29:49

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/

ssss28
Starting Member

18 Posts

Posted - 2008-04-15 : 16:37:34

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-15 : 16:45:47

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/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources