none
Unable to connect to Server; IIS and PHP working

    Question

  • I recently loaded a new machine with my php, database, and MICROSOFT SERVER EXPRESS 2005. I have this same set of programs working on my other computer (both run WINDOWS XP PRO). I can see the database in the MICROSOFT SQL SERVER MANAGEMENT STUDIO EXPRESS. I see the tables and can get the tables while in SQL SERVER EXPRESS MANAGEMENT STUDIO, so I know the table is out there.


    SELECT [ID]
          ,[SubmitterID]
          ,[DateSubmitted]
          ,[NumPostDays]
          ,[NoticeText]
          ,[ExpirationDate]
          ,[LastEditDate]
      FROM [CabrilloDJD].[dbo].[Notices]

    gets me the correct table information

    I've looked at the permissions and users I set up to use the database and all seems well (I think I'd get a different error if I put in bad username, password).

    What I get when I run my standard program from index.php on the mssql_connect() statement: (works on my other system):

    2007-05-03 18:42:04 Ece Error: Level=E_WARNING mssql_connect(): Unable to connect to server: VAIO-MUSIC\SQLEXPRESS in file D:\Deb ....oDJD\globalVariables.php on line 617

    I tried:

    select * from sysservers

    and got 0    1089    VAIO-MUSIC\SQLEXPRESS    SQL Server    SQLOLEDB    VAIO-MUSIC\SQLEXPRESS    NULL    NULL    2007-05-03 10:16:40.810    0    0    NULL    NULL    0    0    VAIO-MUSIC\SQLEXPRESS             1    1    0    0    0    0    1    0    0    0    1    0    NULL    0

    I run in loopback mode using localhost. For example,

     http://localhost/CabrilloDJDSite/test.php

    gets me the correct PHP INFO. I include here the section for MSSQL:

    mssql

    MSSQL Support enabled
    Active Persistent Links 0
    Active Links 0
    Library version 7.0

    Directive Local Value Master Value
    mssql.allow_persistent On On
    mssql.batchsize 0 0
    mssql.compatability_mode Off Off
    mssql.connect_timeout 5 5
    mssql.datetimeconvert On On
    mssql.max_links Unlimited Unlimited
    mssql.max_persistent Unlimited Unlimited
    mssql.max_procs 25 25
    mssql.min_error_severity 10 10
    mssql.min_message_severity 10 10
    mssql.secure_connection Off Off
    mssql.textlimit Server default Server default
    mssql.textsize Server default Server default
    mssql.timeout 60 60

    This is exactly what comes up on my working system.

    I've compared many of the screens on TCP and SQL Server and they are the same except for system names and base disk name. (I use the same directory structure on both, though one has C: drive as the base and one is on DSmile

    I've set this up a few times. I'm primarily a php programmer, but I've put together a few systems (built from components) and loaded a lot of software on systems. I've taken the MICROSOFT for administrators course but still have very seldom installed a server. I work on a little home system (2-3 computers) and do not have a static IP. Everything just runs on local host.

    Anyway, the localhost and php are both working or I wouldn't be able to run the test.php file which just does phpinfo() if those aren't working.

    Any hints greatly appreciated. deb.sites@sbcglobal.net
    Friday, May 04, 2007 2:16 AM

All replies

  • Could you please post your connection string here ?
    Is the SQL Server Browser Service started on the machine ?
    Are remote ocnnections for tcpIP enabled for the instance (see the screencast on my site for more information about enabling remote connections)

    Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, May 04, 2007 7:05 AM
  • Thank you for your response.

    I was enable to view your TCP/IP screencast - I get a message that (I think) says cannot find the screencast - my tiny bit of French and Spanish leave me unprepared for what I think must be German or Dutch. Anyway,

    Here is more information.

    As  I said, I have a second machine setup running this stuff.

    Using the SQL Server Configuration Manager, I checked that the following settings on the same on both machines (they are):

    SL Server 2005 Network Configuration:
        Protocols for SQL Express
            Shared Memory Enabled
            Named Pipes Disabled
            TCP/IP Disabled
            VIA Disabled

    SQL Native Client Configuration
        Client Protocols
           Shared Memory 1 Enabled
           TCP/IP 2   Enabled   Default Port 1433  Enabled yes Keep Alive 30000 Keep Alive Interval 1000
           VIA disabled.

    Since I am running on the same machine in loopback, I don't think I need more than that on the TCP/IP.

    The firewall is OFF on the machine via Microsoft and I unplugged the cable to the SBC Modem/Firewall and ran the test and get the same result. I did add the sqlserver.exe to the list of exceptions on the Microsoft Firewall for when it is on.

    The SQL Server Express is running. (according to Services on the Administrative Tools Services list).

    The Users of the database has CabrilloDJDUser (with the password set for SQL Authorization) as db_owner.

    Here is the line that lets me connect to the database:

     
      $ECEServer = 'VAIO-MUSIC\SQLEXPRESS';
      $datasource = 'CabrilloDJD';
      $ds_un = 'CabrilloDJDUser';
      $ds_pw = 'DJDallow';


    $msconnect=mssql_connect($ECEServer, $ds_un, $ds_pw);

    Thanks again.

    Friday, May 04, 2007 4:52 PM
  • Hi,

     

    I'm not very experienced with networking Sql Express, except for probably finding more problems than most. The following link may be of assistance because it got me up and running.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1576999&SiteID=1

     

    Tailor

    Wednesday, May 09, 2007 11:14 AM
  • Thank you for the suggestion. Unfortunately, it didn't work for me.

    I did check in the configuration manager that remote connections are allowed. I checked the allow CLR  box.

    I also tried reinstalling the server and database but that didn't help.

    I am not a real "server" on the network (just local server on the same machine). I do have the server browser running.

    I also tried the suggestion on another forum site of using the .\sqlexpress name instead of the name of my machine but that didn't help either.


    Since I have it up and running on my other machine, perhaps it is something really simple that I'm not even aware of (some restriction in the web browser? Some other network settings?)
    Though I have tried to go through these and compare on the two machines and everything I've checked thus far is the same.

    Any other suggestion greatly appreciated.
    Wednesday, May 09, 2007 2:27 PM
  • Hi Deb,

     

    Since this is all working on one computer we need to keep digging to figure out what is different between the two. Here are a couple things to look at:

    1. Double check that CabrilloDJDUser is a Login (Security folder under the Server name in Management Studio) and not the name of the database user. There are two levels of security in SQL, the Login is what allows you to connect to the server and the database user gives you access to the database. Once you've confirm that CabrilloDJDUser is the login, check the user mappings to ensure that the Login is mapped to a Database User that has permissions in your database.
    2. Check the SQL error log after you make an attempt to connect to see if it has better information on the failed connection. The error being returned by PHP doesn't seem to have all the information I'd expect to see in a normal SQL error, so it looks like the system is eating part of the message. The error logs are most likely located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG if you installed SQL Express using default settings.

    Let's see what comes of this and go from there.

     

    Mike

    Wednesday, May 09, 2007 2:30 PM

  • Again, thanks for the response. Everything here is true for BOTH systems.

    AT the TOP LEVEL Security under SQLExpress tree,

    CabrilloDJDUser shows up under Security, Logins, with default database set to CabrilloDJD, using SQL Server authentication.

    CabrilloDJDUser Server roles is dbcreator

    User mapping has map to CabrilloDJD, User CabrilloDJDUser, Default Schema dbo with Dababase role membership for CabrilloDJD as db owner and public.

     Securables has nothing marked and Status has Permission to connect to database engine as grand and login as enabled. 

    SQL Server authentication has the login is locked out box grayed out (disabled) and NOT checked.

    I'm not getting the bit about two different entities - the login and the user. It seems that CabrilloDJDUser is both (or maybe the php code invokes a "guest" status?) When I created
    CabrilloDJDUser, I seem to recall putting it in the Logins and then making it somehow as a user of the database.

    I'll look at your original message again (I can't see it from this screen). and post more if I think of something else you asked.

    debra  deb.sites@sbcglobal.net

    UNDER THE DATABASE USER SECURITY Tab
    Under Users, CabrilloDJDUser shows up again. On this one, General has Default schema dbo, with dbo_owner checked, Securables blank, Extended Propertied Database: CabrilloDJD

    Note: On the system that works, there is an additional user CabrilloDev which is setup exactly the same as CabrilloDJDUser with only change being password difference. I don't use this User anymore. The code that opens the database uses CabrilloDJDUser as the User/password on both machines.

    I setup to log both success and failed logins then stopped and restarted the sql service.

    Then I ran my php file on both systems again.

    I tried running the php, then looked at the log files on both.
    2007-05-12 10:05:14.60 Server      Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
        Feb  9 2007 22:47:07
        Copyright (c) 1988-2005 Microsoft Corporation
        Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    2007-05-12 10:05:14.60 Server      (c) 2005 Microsoft Corporation.
    2007-05-12 10:05:14.60 Server      All rights reserved.
    2007-05-12 10:05:14.63 Server      Server process ID is 2312.
    2007-05-12 10:05:14.63 Server      Authentication mode is MIXED.
    2007-05-12 10:05:14.65 Server      Logging SQL Server messages in file 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2007-05-12 10:05:14.65 Server      This instance of SQL Server last reported using a process ID of 264 at 5/12/2007 10:05:11 AM (local) 5/12/2007 5:05:11 PM (UTC). This is an informational message only; no user action is required.
    2007-05-12 10:05:14.65 Server      Registry startup parameters:
    2007-05-12 10:05:14.66 Server           -d d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2007-05-12 10:05:14.66 Server           -e d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2007-05-12 10:05:14.66 Server           -l d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2007-05-12 10:05:14.66 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2007-05-12 10:05:14.66 Server      Detected 1 CPUs. This is an informational message; no user action is required.
    2007-05-12 10:05:14.96 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2007-05-12 10:05:15.11 Server      Database mirroring has been enabled on this instance of SQL Server.
    2007-05-12 10:05:15.19 spid5s      Starting up database 'master'.
    2007-05-12 10:05:15.44 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2007-05-12 10:05:15.73 spid5s      SQL Trace ID 1 was started by login "sa".
    2007-05-12 10:05:15.77 spid5s      Starting up database 'mssqlsystemresource'.
    2007-05-12 10:05:15.80 spid5s      The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
    2007-05-12 10:05:16.26 spid8s      Starting up database 'model'.
    2007-05-12 10:05:16.27 spid5s      Server name is 'VAIO-MUSIC\SQLEXPRESS'. This is an informational message only. No user action is required.
    2007-05-12 10:05:16.30 spid5s      Starting up database 'msdb'.
    2007-05-12 10:05:17.32 spid8s      Clearing tempdb database.
    2007-05-12 10:05:17.76 Server      A self-generated certificate was successfully loaded for encryption.
    2007-05-12 10:05:17.79 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2007-05-12 10:05:17.79 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    2007-05-12 10:05:17.79 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
    2007-05-12 10:05:17.80 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2007-05-12 10:05:18.13 spid8s      Starting up database 'tempdb'.
    2007-05-12 10:05:18.38 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2007-05-12 10:05:18.43 spid11s     The Service Broker protocol transport is disabled or not configured.
    2007-05-12 10:05:18.43 spid11s     The Database Mirroring protocol transport is disabled or not configured.
    2007-05-12 10:05:18.58 spid11s     Service Broker manager has started.

    ------------------
    on the system which works, the log also had
    Starting up database "CabrilloDJD".

    Note that the log on this machine (above) which connect to the database  did NOT have anything about rejecting the connect request.


    Saturday, May 12, 2007 5:43 PM
  • Thanks Deb,

     

    It looks like both copies of SQL Server have the same Login/User informaton and you've already indicated that you can connect succesfully to both using Management Studio, which would indicate that SQL Express in general and you database specifically are working correctly. That puts the problem somewhere in the actual attempt to connect.

     

    Could you double check the Network Protocols on both machines and compare them? I'm specifically interested in the TCP/IP protocol. When you connect to SQL Server, you use something called a Provider. The Provider defines the connection technology you are using, such as ODBC, OLEDB, etc. I'm not familiar with PHP, so I don't what Provider is being used or how you configure the Provider, but many Providers require the TCP/IP protocol to be Enabled in order to connect to SQL Server. I know you reported this already, but I want to double check to be sure because thats the next most obvious reason for a connection failure.

     

    Mike

    Saturday, May 12, 2007 6:04 PM
  • Local  area connection properties:

                RPC Service: Windows locator

     

     

     

    System which works also has : VMWare Bridge Protocol (not on system which doesn't).  Also, the system which doesn't work has firewall off, and I even run with network cable disconnected sometimes to check that. Since I'm running in loopback, I should be able to run without network cable. My test php files still runs even without the cable in).

     

    File and printer sharing for Microsoft Networks checked

     

    QOS Packet Scheduler checked

     

    Microsoft TCP/IP version 6 checked

     

    TCP/IP Properties

                Obtain IP address automatically

                Obtain DNS server address automatically

                            Advanced: DHCP Enabled, Automatic metric checked

     

                DNS append primary and connection specific dns suffixes with append parent suffixes checked

                Register this connections’ addresses in dns checked

                WINS enable lvhost lookup checked

     

                            ADVANCED IMPORT LM HOSTS Button

     

                                                    (Both systems have look in CD which is not mounted)

     

                Options

                            TCP/Ip Filtering

                            Properties: Enable TCP?IP filter all adapers NOT checked

                            Permit all checked on all three: TCP Ports, UDP Ports, IP Protocols



    Do you know of some little piece of html code I can write that DOESN"T use PHP to open my database - this would tell me if the problem is my php setup for sql. I hadn't thought it could be that since the php obviously works when it doesn't open the database and the php code is identical on both systems (except for the directory setup for each database).



    debra

    Sunday, May 13, 2007 7:23 PM
  • Hi Deb,

     

    I wasn't clear, I wanted you to check the TCP/IP settings for SQL Server, you can do this by launching the SQL Configuration Manager. I'll work on a script that will test the connection to SQL without using PHP.

     

    Mike

    Monday, May 14, 2007 2:33 AM
  • Same for both machines.

     

    SQL Server Configuration manager

                SQL Server 2005 Network Configuration

                            Protocols for SQLEXPRESS

                                        Shared memory enabled

                                        Named Pipes disabled

                                        TCP/IP Disabled

                                        VIA Disabled

                SQL Native Client Configuration

                            Client Protocols         

                                        Shared Memory Enabled

                                        TCP/IP Enabled

                                                    Default Port 1433

                                                    Enabled yes

                                                    Keep Alive 30000

                                                    Keep Alive Interval 1000

                                        Named Pipes

                                                    Default Pipe  sql/query

                                                    Enabled yes

                                        VIA

                                                    Diabled (default NIC 0   Default Server 0:1433   Enabled NO)

                            Aliases

                                                    None

     

    I don’t know how to check on the port 1433 availability/use. I had assumed that the SQL Server Management Tool used the same “server port” but perhaps not.

     

     

    Monday, May 14, 2007 2:50 PM
  • Hi Deb,

     

    Just in case you didn't get to this info in my previous post, I'll repeat in full. Note that SqlServerExp does NOT listen on port 1433 as a default.

     

    The following applies to an Sql Express instance on a Small Business Server 2003.

     

    It took me many frustrating weeks to stumble upon the reason why I couldn't connect to SBS from my PC.

     

    Sql Server defaults to listening on Port 1433  ( and Browser listens on port 1434) HOWEVER, Sql Express listens on a dynamic port, which when it opens, looks for the last port opened, but can change it to another, so there is nothing concrete to work to.

     

    To overcome the problem you need to log in to the SBS as Administrator, or get the administrator to do this for you. You will also need the name of the SbServer.

     

    The only way I know to locate the port being used, is to go to the SBS server and connect to the SqlExpress instance running on that machine.

     

    Then run the following query 

      

     SELECT serverproperty('ERRORLOGFILENAME')    which will give you the location of the error log file. Open this file in notepad and look for similar details to the following line.

     

    2007-04-05  15:50:30.71 SERVER  Server is listening on [ 'any' <ipv4> 23935]       The number which will be in 23935 position will be the port being used.

     

    When you get that port number, go to the client machine, and try the following command from the command prompt.

     

    telnet  nameoftheSBSserver  23935   substituting the port number you located.

     

    If you get a blank screen, your port is opened. Press Ctrl + ] > enter, then type quit> enter to get out of telnet.

     

    Get to that point and it's a start. Then I recommend the next step if you are able.

     

    Specify a static port for accessing SqlExpress Server.

     

    On the SBS Server, launch Sql Configuration Manager.

    Click on SqlServer Network Configuration

    Right Click on "Protocols for SqlExpress"

    Right Click on the "TCP/IP" child node

    Click on Properties

    Click on IPAddresses tab

     

    Bottom section should have a greyed out heading called IPAll.

    Delete the entry for TCP Dynamic Ports and leave blank

    In TCP Port value type a number , for example, 2333

     

    Then Stop and Restart Sql Express

     

    To check that you can connect remotely use the following SQLCMD from your remote computer.

     

    SQLCMD -E -S YourServerName\SQLEXPRESS,2333   The comma between SqlExpress and 2333 lets the server know it's a port.

     

    Hope the above information helps you a little.

     

     

    Friday, May 25, 2007 9:31 PM
  • Hi Deb,

     

    Sorry for going dark on you for a bit, things are crazy busy here so I got pulled onto other things.

     

    I'm out of idea on what could be going wrong here. SQL Express certanly looks to be configured identially on both machines and everything seems to be working as expected. I don't know enough about PHP to troubleshoot anything that might be happening on that side, nor do I know much about IIS.

     

    It seems that the problem must be in either IIS or PHP configuration. Given that your PHP application is a copy of one that already works, I would suggest taking a look at the IIS configuration on both computers to see if you can identify something that is different on the machine that fails.

     

    Mike

    Saturday, May 26, 2007 7:56 PM
  • Dear Deb,

     

    I'am facing the same problem. What I found out so far is that there is a difference in the authorisation. In one case you have a windows authentication and in the other case a sql-server authentication. In the first case the user you provide in the mssql_connect() function is not used. Instead 'serverName\I_USR_serverName is used. I do not know the solution yet but it must be possible to force php to one of both authorisation modes.

     

     

    Greetings,

    Tuesday, June 05, 2007 3:06 PM