SSDN - Can't connect to DB - MS SQL Server 2005 Express

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 01:55 PM

Perhaps this forum, being XQuery, may be appropriate.

I am trying to connect my Microsoft SQL Server 2005 instance to Stylus Studio 2007 Ent R2, having enabled the TCP/IP and allowing port 1433 in my Windows XP SP2 firewall (? is that necessary).

I assume I need to pretend the SQL Server is a 2000 one. I know that I can use the Windows ODBC tool to create a System DNS that uses the Microsoft SQ Server 2000 driver, with the SQLEXPRESS database, so I am using TCP/IP with port 1433 and the following:

The Connection Settings I have are -
Database type: Microsoft Sql Server
Username / password: the same as my Windows Authentication (ie, the way I log onto Windows XP).

No success. Can someone give me instructions, please?

Ian

Minollo I.

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Minollo I.
Date: 24 May 2007 02:00 PM

What is the error you are receiving?

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 02:10 PM

If I create a MSSQL Server db, and use the default //localhost:1433 then it is
"[XQuery][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect"

I am using my Windows Authentication logon username & password. Port 1433 is open

Ian

Minollo I.

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Minollo I.
Date: 24 May 2007 02:28 PM

Apparently the TCP/IP transport is not enabled on your SQL Server Express installation. Not sure the same naming/tools apply to the 2005 Express edition, but you should be able to enable the TCP/IP protocol through its "Network Configuration" settings.

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 02:41 PM

As far as I can see, TCP/IP is enabled - I will try to attach a screenshot of SQL Server Configuration Manager that shows this is so.

BTW I closed down Stylus Studio and restarted it, but the firewall and the SQL Server Config utility had been set to allow / enable TCP/IP and port 1433 well before.

Ian

TCPIP_Enabled.jpg
SQL Server Configuration Mgr

Minollo I.

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Minollo I.
Date: 24 May 2007 02:45 PM

Cick on: Protocols for SQLEXPRESS (under SQL Server 2005 Network Configurations).
Is TCP/IP enabled there?

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 02:36 PM

Another try: this time, used //127.0.0.1:1433

Message was: "[XQuery][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect"

Ian

Minollo I.

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Minollo I.
Date: 24 May 2007 02:43 PM

The firewall cannot play a role working with a server on localhost. The problem you are describing is that the server is not accepting TCP/IP connections on port 1433...

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 04:05 PM
Originally Posted: 24 May 2007 04:02 PM

The server is allowing TCP/IP logins - I can select that in SQL Server Management Studio Express (SSMSE), and SSMSE does login OK.
And I changed to "mixed-mode" server authentication (SQL Server and Windows Authentication mode) - but no change (Stylus doesn't login).

Of course I stopped/started the service.

SSMSE allows me to test these things. So, I can change Windows XP user and then try logging in, test permissions, etc - all in that interface.

Maybe it is the format that Stylus expects for username/password?

Assuming I have set the server OK in Stylus Studio - ie, that localhost:1433 is sufficient - then just what should my logon username/password be?

eg, if my machine name is (say) PENTIUM and my logon username for Windows XP is FRED, and password for Windows logon is NerK123 -
then is this formatted right?

Username PENTIUM\FRED
Password NerK123

The way SQL Server is set up is simply Windows Authentication (though as I said above, I have enabled mixed-mode).

Ian Thomas

Minollo I.

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Minollo I.
Date: 24 May 2007 05:22 PM

Ian,
just to be completely sure you saw my message, that got listed in a strange position by the forum, have you verified this?

Cick on: Protocols for SQLEXPRESS (under SQL Server 2005 Network Configurations).
Is TCP/IP enabled there?

Thanks.

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 08:19 PM

Yes (see attached).

Thanks for the repeat information (and other help) - I didn't see that message, earlier.

Still no progress.

But glad to hear the SQL Server 2005 CE will "probably just work", too.

Ian

TCPIP_Enabled2.jpg
Protocols for SQLEXPRESS

Minollo I.

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Minollo I.
Date: 24 May 2007 09:15 PM

Can you try following the steps described here:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=kb400255

(the underlying JDBC drivers in ColdFusion are the same that Stylus Studio/DataDirect XQuery use)

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 24 May 2007 10:41 PM
Originally Posted: 24 May 2007 10:28 PM

Yes, I have just done that (see attached). I followed it closely (but just what is meant by "all applicable IPs" in Step 8, I don't know; I assume what I have done is right).

As an aside, in SSMSE console I could always connect using TCP/IP so I am not sure what this has achieved. Nevertheless, I will try anything.

I set the localhost (127.0.0.1) which is IP3, so its TCP Port is 1433. I did not set the TCP Dynamic Ports.

----- Note -----
And the IPAll (the last IP listed on the Config Mgr TCP/IP Properties page) has been left untouched (as with others, which give access to LAN and remote locations). Its setting is TCP Dynamic Ports = 1103, and TCP Port = blank.
----- Note -----

Lastly, I did click Apply, and restarted the service, closed the Config Mgr and restarted it to see that Port 1433 was set and enabled (as in the attached screenshot).

I'll seek some advice on these settings, also.

Ian

Stylus-cannotconnect.jpg
IP3 - TCP POrt=1433

Ivan Pedruzzi

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: Ivan Pedruzzi
Date: 24 May 2007 11:00 PM

Ian

Please try the following URL and for user-name just a space

//localhost\SQLEXPRESS;AuthenticationMethod=Type2

Hope this helps
Ivan Pedruzzi

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 25 May 2007 09:15 PM
Originally Posted: 25 May 2007 09:00 PM

Ivan
When I saw that, I thought "Eureka!" - it seems logical.
Unfortunately, it has immediately crashed SS (see attachment).

(but also see next post)

On SS's auto-restart, the DB server connection exists and username (one space) and blank password works fine.

Thanks both of you at Stylus Studio Support, for your patience.

Ian

SS-CrashnewURLstring.jpg
Crash

(Deleted User)

Subject: Can't connect to DB - MS SQL Server 2005 Express
Author: (Deleted User)
Date: 25 May 2007 09:13 PM

Yesterday I sought advice from local SQL Server users list on setting IPs etc, and in the interim encountered some problems unique to the SQL Server Configuration Manager:
If the TPC Port is given a value = 0 when the TCP Dynamics Ports value is non-zero, then one or more SQL Server services (eg, the SQLEXPRESS and / or the SQL Reporting) shut down and cannot be re-started.

[ There are a number of additional settings for the SQL box that i applied. I will document them for my own purposes, but it might be helpful for users of Stylus studio wanting to connect to Microsoft SQL Server 2005 Express Edition to have a definitive method to follow. ]

Having remedied that, I found that the Windows Firewall could not be re-started.
This turned about to be a problem with the Windows Update Automation patch (recently, in May2007) from Microsoft - the command "svchost.exe -k netsvcs" just would not succeed.
(see recent posts at the Microsoft WSUS Product Team Blog)
A Windows Update applied a 180K patch which fixed this problem.

Ian

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.