MS SQL Connection String Format

Can I just confirm that I have the string in the right format for connecting to a Microsoft SQL Server as I am getting the following:

“Failed to launch service “uat”: CDbConnection failed to open the DB connection.”,

I’ve checked that SQL is responding to port 1433 by connecting via telnet to the port and it gives me the expected blank screen.

sqlsrv:Server=192.168.100.2;port=1433;Database=UAT

Oddly I am having to use Remote SQL rather than Local as the option doesn’t appear in the list of services.

go ahead and remove port number.

also, do you have the sql native driver installed?

  • Mark

Ok so the connection string is

sqlsrv:Server=192.168.100.2;Database=UAT

Wouldn’t the native driver be installed as part of the SQL Server 2008 R2 installation? They are both installed on the same machine.

Thank you so much for your help.

If they’re both on the same machine, native driver shouldn’t be required. Are you able to tel net the ip and port and get a result?

Yes I can telnet and connect although it only gives a blank telnet screen, which I understand is what should happen.

good. that’s what we want. and you’ve added the drivers to your php.ini file?

No I didn’t see that mentioned in any of the guides to set up REST for SQL Ill take a look at the php.ini

http://technet.microsoft.com/en-us/library/cc296203(v=sql.105).aspx

Follow these steps to create a connection to MS SQL Server:

note: This connection is applicable to all Operating Systems.

1)      Start by making sure everything on the SQL Server side is properly configured.

Please follow this guide:

2)      Download the SQL Server connection drivers for PHP

a.       Download both the SQLSRV driver and also the PDO driver:

i.     php_sqlsrv_54_ts.dll
                                                         
ii.    php_pdo_sqlsrv_54_ts.dll

note: This will be based on the PHP version you’re running (5.4)

b.   Put these files in PHP -> EXT directory

3)      To enable these drivers, add the following lines to the end of you php.ini file, which is located in the PHP folder

;sqlserver connection driver

extension=php_sqlsrv_54_ts.dll

extension=php_pdo_sqlsrv_54_ts.dll

4)      Restart Apace

5)      Create a service within your DSP to connect to MS SQL Server.

a.       Connection strings are auto-generated based on host, port, and database name.

Send an email support@dreamfactory.com if these steps don’t work.

granted since you’re connecting to a local instance of sql server you don’t need to worry about the native driver installation…

correction this is not true. you still need the native driver installed when using this version (or any version) of sql server.

Still not working, but I have emailed support@dreamfactory.com

Looks like we were able to get you up and running (by removing the port…)!

Would also like to point out that if the following steps listed above do not work for others, the solution is typically related to not having the native client installed. This normally is packaged into installations of 2012 or newer, but if using 2008 or any year prior to that…you’ll need to download the native client associated with your specific version of MS SQL Server.

Working on setting up a wiki that will give a bit better instruction.

Thanks,

  • Mark
1 Like

I can confirm that PHP Sql drivers are required to be installed manually.

Yes, they are. I have a document for installation procedures if you’re still stuck but it looks like you’ve made a connection already just not up to performance standards as of yet…

Shoot me that config data and we will go from there.

Thanks,

  • Mark

Hi Mark,

I am having the same issue. I am able to connect to my SQL Server 2008 R2 through the free hosted service https://dsp-storenmove.cloud.dreamfactory.com but not from my Amazon EC2 instance (Tried both ubuntu and windows server) using Bitnami installer. I have checked the 5 steps but not able to figure out the issue.

J

Hi @jayp, what is logged in your DSP’s logs when the connection attempt to SQL Server fails? If you are using the dblib driver, you will need to specify the port in your connection string unless your DB is hosted on port 4000 (the default for the dblib driver).

1 Like

Hi Jeff,

That helped a lot. Once I gave “dblib:host=hostname:1433;dbname=dbname” It worked. This should be documented to help other users. also surprised as port is not required in DSP cloud server but in Bitnami installation.

2 Likes

Ok after a couple of hours getting re-acquainted with PHP :confused:

running on x64 win 10 box you need to get the php-sql drivers with correctly compiled version 2012 local db & remote 2014 on 2012 r2 etc

http://www.microsoft.com/en-au/download/details.aspx?id=20098

sqlsrv32 for php 5.5

Added them into php.ini

removed any junk old vers

local & remote sql svcs rocking :smiley:

Also suggest getting pplz to do a php -v to find the complie probs

1 Like

More about…Connection String

Wells