Can't connect to SQL Server 2005

Hello,

I have a problem with connecting to SQL Server 2005. I have a small network at home with a WLAN router connected to Internet. My notebook computer and desktop computer are connected to router. My SQL Server 2005 is installed on notebook computer and Microsoft Server Management Studio is installed on desktop computer. I wanted to add login so I could connect to it from desktop pc and a problem came up. When I tryed to add account (from desktop pc) to local group on notebook pc I couldn't do that becouse there wasn't offered desktop pc as a location. Also, when I try to make connection string in visual studio, i can see sql server but i can not choose database. Is this becouse of router or is it posible to do that only in a real server(domain)

Answer this question

Can't connect to SQL Server 2005

AdamFinzel
There are detailed step-by-step instructions to follow on the following post for dealing with SQL Server connectivity/connection issues:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=302440&SiteID=1
Warren Blackwell
Thanks for help. I think I'll use SQL Server Authentication. I have another problem. When I try to make connection string in visual studio 2005 I can select sql server (on another computer) but when I want to select database it says that the sql server is not responding. Do you maybe have solution on this problem
Mandy26
From what you have said, your firewall on SERVERMACHINE was blocking the connection. So, open up the port for SQL Server on SERVERMACHINE. Each instance of SQL Server is set to listen on a different port. You are not using a named instance (ie. you're using the default instance). The default port for the default instance is 1434. You can confirm this is the correct port by following the instructions below:
Using SQL Management Studio, connect to the database, and execute the following query:

Use master
Go
Xp_readerrorlog
This will give you a heap of information, but the line you're looking for is that below:
2006-03-24 14:47:38.290 Server Server is listening on [ ***.***.***.*** <ipv4> 99999].
The port number is displayed in purple above

INF: TCP Ports Needed for Communication to SQL Server Through a Firewall

http://support.microsoft.com/default.aspx scid=kb;en-us;287932

RayV

Probably you didn't understand what I was saying. I can connect to SQL Server with Management Studio (with Windows Authentication) only if I enable Guest account and create Login ( <domain>\Guest). When I tried to add account from another computer to login I couldn't see that other's computer location and when I tried to add it manualy (e.g. <domain>\Korisnik) I got the message that like "Windows NT user or group doesn't exist".I've been reseaching little bit and I tried to connect with SQL Server Authentication and there was no problem. I read that it is safer to use windows authentication so I am looking for an answer.

P.S. I have configured correctly network protocols.

GirishP

Jens Suessmeyer wrote:
Thats one of the solution which in IMHO should not be used. Nothing personal, but that always sounds to me like a misdesign or security leak rather than a feature of Windows. This was valid in SQL 2000, did anyone check if this still applies in SQL2k5.

The ability to 'share' user accounts across machines is intrinsic to Windows itself, and not really related to SQL server. It will work with all Windows functions.

Of course it's not a practice that's typically used in a production environment for obvious reasons (mostly related to security & managability), but for small home/development environments, it's acceptable. Not all environments are big enough to warrant the use of a domain server, and in these cases, in order to use Windows Authentication, it's necessary to use this method for cross-machine access. Microsoft recommends using Windows Account logins as opposed to SQL logins for security reasons (ie. passing the password in clear text in the connection strings).

An important feature of these forums is to explain the features that are available, along with the facets therein, in order that others may make a decision based on their circumstances, so for this reason I think it's important to explain how to do something, if someone wants to know, even though the procedure explained may not be best pratice.

emates

Firstly, please try reviewing the instructions and situation as explained in the previously given thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=302440&SiteID=1

Then, if the steps and instructions don't help, then please provide more information on your scenario. Refer to your machines as SERVERMACHINE (the machine with SQL Server installed) and REMOTEMACHINE (a machine other than the SQL Server machine from which you are trying to connect - if any). Also include the connection string that you are trying to use, and details of the results of an attempt to use this conenction string on both machines (SERVERMACHINE and REMOTEMACHINE). Further, indicate your settings on both machines with regard to protocol settings (Named Pipes/Shared Memory/TCP-IP --- refer to the above thread for details).

Again, please try reviewing the instructions in the above thread, as it is explained in detail.

SamScratch

Sorry for my stupidness. I'm new to sql server and I stll don't know to use some phrases. Sorry again.

1.) PROTOCOL: Named Pipes, SERVERMACHINE

I connected sucessfuly with SSMS and connection.udl (SQL Server Authentification)

Here is the connection string used with connection.udl:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Test;Initial Catalog=ApressFinancial;Data Source=RABUZIN_2

I checked Event Log and everything looked ok. It displayed my connection.

2.)PROTOCOL: Named Pipes, REMOTEMACHINE

I connected sucessfuly with connection.udl. Here is connection string:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Test;Initial Catalog=ApressFinancial;Data Source=RABUZIN_2

But when I tried to connect with SMSS there was an error: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". The event log didn't trace anything.

3.) REMOTEMACHINE

When I attempt to navigate to the server in a connection dialog using the UDL method I can see my server's name.

4.) I can explore SERVERMACHINE from REMOTEMACHINE, protocols are properly set up, and service is running.

Martin Ly

I couldn't connect to server with SSMS probably becouse I was logged on as limited user (my mistake). Now it works. But then I still could't connect with Visual Studio 2005(I was able to see only server name). Then I disabled firewall on SERVERMACHINE and now it works too. I can see server name and database. Now, the only problem is how to configure firewall Do you have any idea Tnx for help.

reom

The fact that you are able to connect to the server, but not to a database indicates that there is a security issue with the account that you are using to connect. Validate that the account that you are using to connect has access to the database to which you wish to establish a connection.

(Typically, to rule out any other issues, try connecting with a sa or Administrator account first, and once connectivity issues are ruled out, then you can concentrate on refining your security settings, and testing other database logins)

DougTaylor

It is possible to do what you are trying to achieve. If you have two PCs on a Windows Network, both of which are not running on a domain, then if you want to use the same account across the two computers, then you have to give them the same password on both PCs --- same user/account name, same password.

Anders Hejlsberg

Thats one of the solution which in IMHO should not be used. Nothing personal, but that always sounds to me like a misdesign or security leak rather than a feature of Windows. This was valid in SQL 2000, did anyone check if this still applies in SQL2k5.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

EvanstonIl

I find it strange that you are able to connect using SQL authentication via the UDL method on the REMOTEMACHINE, but not using Management Studio (SSMS) on the same machine. Please check/answer the following:

Check that you don't have any firewall blocking outgoing requests from SSMS on REMOTEMACHINE
When you attempt to connect in SMSS on REMOTEMACHINE, (eg. within object explorer, click Connect -> Database Engine), can you see SERVERMACHINE (RABUZIN_2 in your case) in the list
On REMOTEMACHINE, click Connect -> Database Engine. Type REMOTEMACHINE in the server name field. Change Authentication to SQL Server Authentication, and enter RABUZIN_2. Set Login to Test, and enter your password if you have one. What happens when you click Connect
If (3) fails, repeat step (3) instructions, but before clicking Connect, click "Options >>". On the Network Protocol lis, choose "Named Pipes". Then click Connect. What happens
Are you able to connect using a connection string from a .NET program (if you are a programmer, and are able to write a simple test program)

ChronoReverse

Hi,

if you are not using a domain contect you can’t connect to the other server/computer via Windows Authentication, because the other server can’t proof your identity. There are scenarios, like impersonating the user cia code (like in c#) to o remote account, but I don’t think that this is a suitable solution for you.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Lee.NET

I tried with my login and with sa login but it didn't work.Then I typed name of database manualy and it said something like "Timeout expired, server is not responding".I repeat I can connect with SQL Server Management Studio.

Can't connect to SQL Server 2005

Answer this question