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.
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
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,
gets me the correct PHP INFO. I include here the section for 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 D
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. email@example.com
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.
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
SQL Native Client Configuration
Shared Memory 1 Enabled
TCP/IP 2 Enabled Default Port 1433 Enabled yes Keep Alive 30000 Keep Alive Interval 1000
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);
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.
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.
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:
- 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.
- 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.
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.
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.
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.
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
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)
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).
Same for both machines.
SQL Server Configuration manager
SQL Server 2005 Network Configuration
Protocols for SQLEXPRESS
Shared memory enabled
Named Pipes disabled
SQL Native Client Configuration
Shared Memory Enabled
Default Port 1433
Keep Alive 30000
Keep Alive Interval 1000
Default Pipe sql/query
Diabled (default NIC 0 Default Server 0:1433 Enabled NO)
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.
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.
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.
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.