8 Replies Latest reply on May 8, 2008 5:47 PM by Turloch O'Tierney-Oracle

    Can't connect to sql server 2005 express

    259480
      I have the latest release of sqldeveloper 1.5.0.51 and want to connect to sql server 2005 express.

      I have added the extension and have set the third party jdbc driver path to
      C:\oracle\sqldeveloper\sqldeveloper\extensions\oracle.sqldeveloper.thirdparty.drivers.sqlserver
      which I hope is correct.
      I have checked the sqlexpress network config and tcp/ip is enabled. I have also made sure that the server and server browser are set to automatic and running.

      For logon, I have it set to Use Windows Authentication, Host name is LOCALHOST and Port is 1433 which is what sqlserver is configured for BUT...

      When I try to logon I get the "Cannot connect to SQL Server on Localhost" error. If I use the machine name - I get the same error message.

      There's no service or sid required for sqlserver so I don't know how it picks up where to find it. The Retrieve database button results in the same error, the drop down button beside it is blank.

      Can somebody tell me what I am doing wrong and how I can connect to SQL Server since I now have to work in both environments.

      Thanks
      Glenn
        • 1. Re: Can't connect to sql server 2005 express
          Turloch O'Tierney-Oracle
          Hi Glenn,

          I think you need a dll.

          From an earlier post by jcoliv:
               Re: sql developer + windows authentication
          Posted: Aug 17, 2007 6:36 AM in response to: user589790 in response to: user589790      
               Click to reply to this thread      Reply

          It is, but it it's complicated - at least from what I have experienced.
          First you need the jtds-1.2.1.jar on your machine (a tutorial here..http://www.oracle.com/technology/products/database/sql_developer/files/viewlets.html) - it doesn't mention that you need to unzip the file (from sourceforge.et) and add the SSO directory to your windows PATH variable.
          It gets complicated depending on the Win Server (e.g. clustered)
          On the Hostname I had to add this "ip adress":1433;instance=CVDEV instead of just the server's ip\CVDEV as you would do in a win connection
          And you get disappointed, from what I found out, you need a connection for each of the databases there (e.g. master, temp, etc) it doesn't show all the databases on the server under the same connection

          -Turloch
          • 2. Re: Can't connect to sql server 2005 express
            259480
            Thanks for your response, but I'm getting a bit confused now

            I have a path set directly to

            C:\oracle\sqldeveloper\sqldeveloper\extensions\oracle.sqldeveloper.thirdparty.drivers.sqlserver\lib\jtds-1.2.jar
            which is right in the sqldeveloper path - and I can see the sql server tab in the connection screen. This is the file that oracle provided as part of the update. In this, there is no SSO directory, just the jtds-1.2.jar.

            I also downloaded the most recent jtds from sourceforge and set it up on another directory in the path, but got the same result so I went back to the oracle one.

            I know that tcp/ip and named pipes are enabled (recomendation from somewhere else) for both the SqlExpress and Sql Native client Configuration Protocols and I've set it up to use both local and remote connections. Since SqlServer Express and SqlDeveloper are both on one machine - the firewall should not be a problem.

            For host, I have tried "localhost", the machine name, the ip address.- all with the same 'Cannot connect to SQL Server on (one of the previous)'.

            I should point out again that this is Sql Server EXPRESS not the big SServer. For Background: Why am I doing this? - we have a large Mineral Deposits Database under way on our Oracle server, but we need to move subsets (or as much as we can fit) into SqlServer Compact database for distribution for our internal staff on their pda's and for clients - so I would like to have SqlDeveloper functional to work on both databases from one central point.

            I hope somebody has an answer
            • 3. Re: Can't connect to sql server 2005 express
              Turloch O'Tierney-Oracle
              Hi gconley,

              jtds is not an Oracle product see:

              http://jtds.sourceforge.net/faq.html search for: Single-Sign-On

              Or search this forum, or the migration forum Database and Application Migrations , for other SQL Server windows authentication references besides the one given earlier.

              -Turloch
              • 4. Re: Can't connect to sql server 2005 express
                Turloch O'Tierney-Oracle
                Hi gconley,

                I assumed your problem was Single Sign On windows authentication but in another thread it appeared that that error was more explicit.

                1/Is Windows Authentication required? If so see:
                Re: JDBC Connection to 3rd Party Database

                2/Can you telnet to the port to (mostly) prove SQLServer is listening?
                Port Open (SQLServer Listening):
                turloch@totierne-ie:~/tmp/fgj/raw$ telnet totierne-pc.ie.oracle.com 1433
                Trying 10.169.112.132...
                Connected to totierne-pc.ie.oracle.com.
                Escape character is '^]'.

                ^]

                telnet> quit
                Connection closed.

                Port Closed:
                turloch@totierne-ie:~/tmp/fgj/raw$ telnet totierne-pc.ie.oracle.com 1434
                Trying 10.169.112.132...
                telnet: Unable to connect to remote host: Connection refused
                turloch@totierne-ie:~/tmp/fgj/raw$

                3/Are there more than on SQLServere instances on that port?

                -Turloch
                • 5. Re: Can't connect to sql server 2005 express
                  259480
                  no, the problem was much simpler - but I would have never thought of doing it.

                  After setting up the jtds driver and pointing to the jar file, on the connect screen, leave windows authentication checked, hostname set to localhost, and in the port box put the following line:

                  1433/databasename;instance=instancename

                  and everything worked. - all the databases were visible in the retrive database section.

                  I found this solution at:

                  http://www.ethergeek.com/blog/2008/04/11/oracle-sql-developer-jtds-driver-and-sql-server-2005/
                  • 6. Re: Can't connect to sql server 2005 express
                    Turloch O'Tierney-Oracle
                    Hi gconley ,

                    We were not sure whether to give that multiple instance workaround out as people were having problems with it and we have not confirmed in house.
                    After setting up the jtds driver and pointing to the jar file, on the connect screen, leave windows >authentication checked, hostname set to localhost, and in the port box put the following line:
                    1433/databasename;instance=instancename
                    and everything worked. - all the databases were visible in the retrive database section.
                    At the risk or getting you to repeat yourself:
                    Can you confirm this works for multiple instance on the same port for SQLServer 2005?

                    -Turloch
                    • 7. Re: Can't connect to sql server 2005 express
                      259480
                      Hi Turloch

                      Can you confirm this works for multiple instance on the same port for SQLServer 2005?

                      Not sure exactly what you mean by this??? Unless it is the following?

                      To check whether everything was showing up I had both the sqlserver 2005 Express LOCAL database open plus 2 instances of the sql developer connected to sqlserver express (one by accident) and I could see every table, view, index and select from any of the sqlserver tables. Not much, but I just got it working this afternoon and I did not experience any problems.

                      So, to me, that's 3 instances at the same time - but I must stress that this is on a LOCAL sqlserver 2005 EXPRESS database. So if you feel that this is a problem for the 'server' version and it can cause problems, then by all means, remove this.

                      I just want to say that the last time we messaged about this problem was about 3 weeks ago, with no answer in sight. Since then I've wasted about 3 more days of time - bit here, bit there - trying things and following suggestions - all leading up blind alleys - and this is the first 'fix'? that worked for me. I wish somebody official had come out and said - this feature doesn't work - don't waste your time on it - we are fixing it.

                      So, indeed, if it is causing problems, by all means remove it - but, I found it out on the web - and it was the first response to my query - so it is not going to be long before you are going to have to deal with a lot of others trying and or suggesting it.

                      I don't envy your position but I only see 2 choices - 1) fix the problem, or, 2) make a sticky that says this is dangerous and it is going to do ?????? - so don't use it.

                      What ever you do, I just want to say that sqldeveloper is the best tool that I have used - and thank everyone for all the hard work that went into it. Can't expect everything to work first time/everytime.

                      Good Luck
                      Glenn
                      • 8. Re: Can't connect to sql server 2005 express
                        Turloch O'Tierney-Oracle
                        Hi Glenn,

                        Sorry I meant connecting to a non default instance of multiple instances of SQLServer all on the same port, not multiple instances of SQLDeveloper connecting to sqlserver.

                        The current GUI does not have a instance name field to differentiate between SQLServer instances on the same port.

                        It was not clear to me that this was the problem you had, when you posted previously.

                        -Turloch