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?
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
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.
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.
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...
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).
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).
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.