Unable to connect to SQL Server 2005

jhjh Posts: 8
edited November 19, 2007 4:45AM in SQL Data Compare Previous Versions
I saw that this has already been asked before, but it seems I've tried everything (and those topics are now locked)...

I'm trying to connect to an SQL 2005 EXPRESS server; I can connect fine using the SQL Server Management Studio, but my attempt in SQL Compare results in an 'invalid connection' error. I also tried:

TCP:servername
servername\sqlexpress (results in a 'server does not exist or access denied error)
IPaddress
IPaddress\sqlexpress

I have verified that TCP/IP and named pipes are enabled, and that TCP/IP is listed number 2 in the client protocol configuration (under shared memory) on the server...

Any help/ideas much appreciated!!
- Jeff
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Jeff,

    You may also want to consider that SQL Express doesn't allow remote connections by default. If the server is on a different machine than SQL Compare, you will need to go into the configuration and allow remote connections.
    · Share on Twitter
  • jhjh Posts: 8
    Yes, I've verified that remote connections are allowed on the server, stopped and started the SQL service, and still get the connection error.

    The next possibility seems to be starting the Server Browser service and creating firewall exceptions, but we don't want to do that due to increased security risks.

    Any other possibilities? I've talked to our dedicated server support and since we can connect via SQL Server Management Studio and this is 3rd party software, they can't really give us support for it but will of course make whatever changes we think need to be made... If only we knew what changes we need!
    · Share on Twitter
  • jhjh Posts: 8
    Ok, apparently one developer here can connect no problem, the other 2 of us can't. I'm on XP as is the developer who can connect, so... now I'm really stumped.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    Is it possible that the other developers are running .NET Framework 2.0, and you're running 1.1? As I recall, the reason for prefacing your connection with TCP: was to work around a bug in 1.1 that chose named pipes network library, even if the server was on a whole other network. Perhaps MS have sorted this out in 2 and 3 versions of ADO .NET?
    · Share on Twitter
  • jhjh Posts: 8
    Probably wouldn't be the .NET Framework, both of us who can't connect are running 2.0 ...
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    What about TCP:Servername\SQLEXPRESS?
    · Share on Twitter
  • jhjh Posts: 8
    Still the same "invalid connection" error.

    Any reason why these two variations would give a different error?:

    servername\SQLEXPRESS = SQL Server does not exist or access denied
    TCP:servername\SQLEXPRESS = Invalid connection
    · Share on Twitter
  • cplummer4cplummer4 Posts: 3
    I'm not using SQLExpress, however I had the same issue. This is what I did to fix it. I had to allow TCP/IP on the server network configuration, it wasn't defaulted to being enabled after a new install of SQL 2005.

    In the Start Menu...
    Microsoft SQL Server 2005 \ Configuration Tools \ SQL Server Configuration Manager

    In the program...
    SQL Server 2005 Network Configuration \ Protocols for MSSQLSERVER \ TCP/IP.

    Right click on TCP/IP and select Enable.

    Close any SQL related programs, restart all the SQL services.

    Note: I did not have to enable named pipes.

    kind regards,
    -Casey Plummer
    · Share on Twitter
  • jhjh Posts: 8
    Hey Casey,

    Thanks for the reply; I checked out the server configuration and TCP/IP is already enabled. Actually, I noted that I verified that named pipes and TCP/IP are enabled in my first post :)

    We just moved offices, so I was hoping it would magically start working, but no luck so far... I'll go back and try everything again and will post if something works or I figure it out.
    · Share on Twitter
  • jhjh Posts: 8
    Problem solved! One of the other developers figured it out (thank goodness).

    In the server field, we have to put:

    serverIP\SQLEXPRESS,1433

    I'd give an explanation for it, but frankly I don't know why this format has to be used. Something about the port number the server's listening on, which isn't dynamic? I have no idea really.
    · Share on Twitter
  • RawdenRawden Posts: 34 Bronze 2
    Does anyone know why this happens on SQLDataCompare, but not SQLCompare? Surely either the problem has been fixed in one, but not the other, or they run differently. ???
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I don't believe Red Gate has done anything at all to 'fix' this. It's the .NET Framework's SQL Server network libraries being flaky; the Framework 2 libraries work much better but there are still problems. Version 6 of SQL and Data Compare use the .NET 2.0 runtime so they function much better because of that.
    · Share on Twitter
  • RawdenRawden Posts: 34 Bronze 2
    Thanks Brian, I'll upgrade. :P
    · Share on Twitter
  • kennyguokennyguo Posts: 3
    Oh does anyone know why this happens on SQLDataCompare, but not SQLCompare? Surely either the problem has been fixed in one, but not the other, or they run differently. :cry:
    you got me thinking clearer here am i gonna be you and your sky as i stand
    · Share on Twitter
  • richardjmrichardjm Posts: 598
    Until the release SQL Data Compare 6 recently it was running on .NET 1.1 however SQL Compare 6 had been released and that ran on .NET2. Now both the latest versions of the product run .NET2 so there should be no difference in the behaviour of the products now.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    The connection components of SQL and Data Compare are exactly the same. If you have different versions (SDC 5, SC 6) then what you are probably seeing is a difference in Microsoft's .NET Framework between versions 1.1 and 2.0, since the v6 SQL products all use Framework 2.0 runtime. This would account for a difference in behavior in the connection components. IMHO the ADO .NET SQL Client components in .NET Framework 1.1 are seriously buggy.
    · Share on Twitter
Sign In or Register to comment.