{"action":"created","issue":{"url":"https://api.github.com/repos/tds-fdw/tds_fdw/issues/96","repository_url":"https://api.github.com/repos/tds-fdw/tds_fdw","labels_url":"https://api.github.com/repos/tds-fdw/tds_fdw/issues/96/labels{/name}","comments_url":"https://api.github.com/repos/tds-fdw/tds_fdw/issues/96/comments","events_url":"https://api.github.com/repos/tds-fdw/tds_fdw/issues/96/events","html_url":"https://github.com/tds-fdw/tds_fdw/issues/96","id":176247300,"number":96,"title":"Failed to connect using connection string my_IP:port with user sa","user":{"login":"eddydev03","id":22101595,"avatar_url":"https://avatars.githubusercontent.com/u/22101595?v=3","gravatar_id":"","url":"https://api.github.com/users/eddydev03","html_url":"https://github.com/eddydev03","followers_url":"https://api.github.com/users/eddydev03/followers","following_url":"https://api.github.com/users/eddydev03/following{/other_user}","gists_url":"https://api.github.com/users/eddydev03/gists{/gist_id}","starred_url":"https://api.github.com/users/eddydev03/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eddydev03/subscriptions","organizations_url":"https://api.github.com/users/eddydev03/orgs","repos_url":"https://api.github.com/users/eddydev03/repos","events_url":"https://api.github.com/users/eddydev03/events{/privacy}","received_events_url":"https://api.github.com/users/eddydev03/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-09-11T14:49:01Z","updated_at":"2016-09-12T00:50:58Z","closed_at":null,"body":"Hello guys, first I want to thank all the contributors for this fdw!!!\r\n\r\nThe context is: I have computer1  which is hosting an SQL server(MSSQL Server 2008 R2) on Windows 7 32bit and a computer2 which is hosting a PostgreSQL 9.5 on Windows 7 64bit on the same home network.  I succeeded with postgres_fdw to query a Postgres server on computer1 from my Posgres server on computer 2. NOW, I am trying to query an SQL server on my computer1 from my Posgres server on computer 2. I am using tds_fdw on windows 7 64bit with the PostgreSQL version of BigSQL.\r\n\r\nHere is my requests:\r\n\r\nCreate extension if not exists tds_fdw;\r\n\r\nCREATE SERVER mssql_svr\r\n    FOREIGN DATA WRAPPER tds_fdw\r\n    OPTIONS (servername 'xxx.xxx.x.xx', port '1433', database 'db', tds_version '7.1');\r\n\r\n\r\nCREATE USER MAPPING FOR postgres\r\n    SERVER mssql_svr \r\n    OPTIONS (username 'sa', password 'xxxx');\r\n\r\n\r\nCREATE FOREIGN TABLE dbi (\r\n    CF_ITEM_ID int NOT NULL,\r\n   CF_ITEM_NAME varchar NULL,\r\n\r\n)\r\n    SERVER mssql_svr\r\n    OPTIONS (table 'dbo.tablemssql', row_estimate_method 'showplan_all');\r\n\r\nCreate table if not exists tablepostgres as\r\nSELECT dbi.CF_ITEM_ID, dbi.CF_ITEM_NAME\r\nFROM dbi\r\nWHERE dbi.CF_ITEM_PATH LIKE 'AAAAABAABAAFAADAACAAA%';\r\n\r\nWhen I run that script on the database where I want the table \"tablepostgres\" via the sql query window in pgadmin3 The error returned:\r\n\r\nFailed to connect using connection string mon_IP:port with user sa\r\n\r\nThe thing that I don't understand is that I can connect and read data from a database in the SQL server on computer2 from my computer1 via Wampserver (3.0.4). I did all the config for sqlsrv and it works. The php code is:\r\n\r\n  try{\r\n // create a database connection\r\n $conn = new PDO('sqlsrv:Server=ip,1433;Database=db','sa','pass'); \r\n // fonction testconn returns Connected if the connection is made.\r\n testconn($conn);\r\n \r\n}catch (PDOException $e){\r\n // report error message\r\n echo $e->getMessage();\r\n}\r\n$req = $conn->query('SELECT * FROM tablemssql');\r\n    while ($r = $req->fetch())\r\n    {\r\n        print_r($r);\r\n    }\r\n\r\nOn google chrome localhost. The table is displayed so the connection and the read permission are enabled though. So I don't really know why I am getting the error \"Failed to connect using connection string mon_IP:port with user sa\" from pgAdmin3. Someone can help me a bit?\r\n\r\nThanks in advance!\r\n\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/tds-fdw/tds_fdw/issues/comments/246216590","html_url":"https://github.com/tds-fdw/tds_fdw/issues/96#issuecomment-246216590","issue_url":"https://api.github.com/repos/tds-fdw/tds_fdw/issues/96","id":246216590,"user":{"login":"eddydev03","id":22101595,"avatar_url":"https://avatars.githubusercontent.com/u/22101595?v=3","gravatar_id":"","url":"https://api.github.com/users/eddydev03","html_url":"https://github.com/eddydev03","followers_url":"https://api.github.com/users/eddydev03/followers","following_url":"https://api.github.com/users/eddydev03/following{/other_user}","gists_url":"https://api.github.com/users/eddydev03/gists{/gist_id}","starred_url":"https://api.github.com/users/eddydev03/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/eddydev03/subscriptions","organizations_url":"https://api.github.com/users/eddydev03/orgs","repos_url":"https://api.github.com/users/eddydev03/repos","events_url":"https://api.github.com/users/eddydev03/events{/privacy}","received_events_url":"https://api.github.com/users/eddydev03/received_events","type":"User","site_admin":false},"created_at":"2016-09-12T00:50:57Z","updated_at":"2016-09-12T00:50:57Z","body":"I created a user \"tds\" with a password, then I did a request on SQL Server in that way:\r\n\r\n    use dbmssql;\r\n    create user [tds] from login [tds] \r\n    exec sp_addrolemember 'db_owner' , 'tds';\r\n    exec sp_addrolemember 'db_datawriter' , 'tds';\r\n    exec sp_addrolemember 'db_datareader' , 'tds'; \r\n\r\nI tried the connection and execute a SELECT statement from the computer that hosts postgres with Wampserver to the computer that hosts the remote mssql: Connection is made, the statement listened and the result is displayed! Here is the code:\r\n\r\n\r\n\r\n$conn = new PDO('sqlsrv:Server=xxx.xxx.x.xx,xx;Database=db','tds','xxx'); \r\n testconn($conn); // -> Connection successful\r\n \r\n}catch (PDOException $e){\r\n // report error message\r\n echo $e->getMessage();\r\n}\r\n//That is the select statement and it prints the result on google chrome localhost\r\n  $req = $conn->query('SELECT * FROM mssqltqble');\r\n    while ($r = $req->fetch())\r\n    {\r\n        print_r($r);\r\n    }\r\n\r\n Actually, I use pgadmin to test the request for tds_fdw but I can also do it by Wampserver. I got the same connection string error.\r\n\r\n//request() does the request trough the connection to a server\r\nrequest($conn,$tds_fdw);\r\nrequest($conn,$create_server_mssql);\r\nrequest($conn,$grant);\r\nrequest($conn,$create_user_mssql);\r\nrequest($conn,$create_foreign_table_cf_mssql);\r\nrequest($conn,$create_mssqltable); // The error comes from here...\r\n\r\nThen, if the connection to the mssql remote server works, what can be the problem when I do the same in pgadmin?? \r\n\r\nPS: I used postgres_fdw plugin for Postgres to remote Postgres via Wamp no problem with that. It works fine, I ve got the tables created in the localhost from selected columns of a foreign table wrapped to a remote postgres server. \r\n\r\nI wanted to get the same mechanism from selected columns of a foreign table wrapped to a remote mssql server. tds_fdw looks the one I want. Even if I want to go deeper with tds_fdw, maybe someone can give me a way to get this mechanism by another Postgres plugin (has to work on Windows)??     "}}
{"actor":{"display_login":"eddydev03"}}