ICF, SQL SERVER 2000 and Windows 2003

Is it possible to configure a W2K3 server's ICF (internet connection
firewall) to allow remote connection of EM (enterprise manager) to SQL
Server 2000? I tried creating 2 new "applications" on port 1433 TCP and
1433 UDP, and was unable to connect. Disabling ICF does allow EM to
connect and function.
Tia - MarkCheck your firewall log to see what is being blocked.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

IBM Informix Ole Db Provider

Hello,

I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.

Thanks,

Matt

===================================

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@.xxxx". (SQL Server Import and Export Wizard)

===================================

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Program Location:

at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)

Moving to the "SQL Server Integration Services", which deals with data transformation issues.

|||

This means your OLE DB provider does not publish Provider_Types schema. The wizard relies on this schema to map data types between heterogeneous data sources.

You may want to try to build your SSIS package manually using OLE DB Source to access your Informix database.

Thanks.

|||

Hello,

Does your solution apply to the linked server as well? Any chance you know if SQL Server 2005 allows setting up an informix 7.3 db as a Linked Server? I now can connect with ADO.NET 2.0 using the IBM.Data.Informix namespace but have not been able to find any documentation on setting up connection through a linked server. FYI, I now have to correct IBM SDKs installed (2.9).

Thanks,

Matt

|||

I am having the same problem. Is there a way to get SQL server 2005 to talk to the Informix 7.3 engine? I get the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Ifxoledbc" for linked server "MINNYME2" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "MINNYME2".

|||

You are in luck. I'm currently still looking into this but have done a significant grind and configured the connection with ODBC. How are you connecting? With the Informix OLEDB? I'm currently waiting for the AIX admin to set something up on the server. He has to run a few scripts to set up the OLEDB properly. I found a page on IBM's site with significant documentation. Please see below. I believe I know what is wrong with my OLEDB connection, it is the set-up. I have a working ODBC now and can help you with that if need be.

http://www-306.ibm.com/software/data/informix/pubs/library/csdk_29.html

Regards,

Matt

|||

Yes, I am connecting using Informix OLE DB. I tried create a linked server with SQL Server 2005 with the IBM Informix OLE DB Provider. Here is my connection info. Did specify something wrong? It seems you abandoned this approach. Should I do the same and try to link the server with ODBC?

/****** Object: LinkedServer [MINNYME5] Script Date: 05/02/2006 11:10:06 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'MINNYME5', @.srvproduct=N'Informix-CLI 2.9 (32 bit)', @.provider=N'Ifxoledbc', @.datasrc=N'bsco@.becton_tcp', @.provstr=N'server=bsco@.becton_tcp;database=bsco;uid=informix;password=tewwetca'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'use remote collation', @.optvalue=N'true'

|||

Here is the code to set up the ODBC linked server. Please read the comments and notes provided throughout. I can help you further if need be. Just so we are on the same page, I'm using

OS: Windows XP Pro.

DotNet Framework: .NET SDK 1.1

IBM software development kit version: IBM CSDK 2.9

Good Luck!

EXEC sp_addlinkedserver

@.server = 'yourServer', -- defined in SetNet32 on tab 'Server information', -- field 'Informix Server'

@.provider = 'MSDASQL', -- DO NOT CHANGE !

@.datasrc = 'yourOdbcConnectionName', -- name of the ODBC connection defined in your system. (you must configure this.)

@.srvproduct = 'Informix-CLI 3.30 (32 bit)',

@.provstr = 'Host=yourHost;Server=yourServer;Service=yourService;Protocol=olsoctcp;Database=yourDB;UID=yourUIDToConnectToAIX;PWD=yourPWD'

NOTE: I'm not 100% sure if a login is needed but i created one.

EXEC sp_addlinkedsrvlogin 'yourLinkedServer', 'false', 'yourWindowsXPUserID', 'yourUIDToConnectToAIX', 'yourPassword'

READ-You need to set up permission for the UserId connecting to the AIX server in the AIX server. I had a AIX admin set me up considering I know little about IBM products.

Run the following query from SQL SERVER to query an ODBC linked server:

SELECT * FROM OPENQUERY(yourLinkedServer, 'select * from yourTable')

|||i done that , but cant insert into linked server tables and this error happens
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
do u know why?

|||

Although I have never tried inserting a row into an Informix Linked Server, I'd assume that it is not possible considering I've seen similar results with other data sources.

|||thank you for replying
also i cant set linked server with oledb informix
when setup linked server with odbc can update, delete and select from linked table but only cant insert into the table

IBM Informix Ole Db Provider

Hello,

I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.

Thanks,

Matt

===================================

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@.xxxx". (SQL Server Import and Export Wizard)

===================================

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Program Location:

at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)

Moving to the "SQL Server Integration Services", which deals with data transformation issues.

|||

This means your OLE DB provider does not publish Provider_Types schema. The wizard relies on this schema to map data types between heterogeneous data sources.

You may want to try to build your SSIS package manually using OLE DB Source to access your Informix database.

Thanks.

|||

Hello,

Does your solution apply to the linked server as well? Any chance you know if SQL Server 2005 allows setting up an informix 7.3 db as a Linked Server? I now can connect with ADO.NET 2.0 using the IBM.Data.Informix namespace but have not been able to find any documentation on setting up connection through a linked server. FYI, I now have to correct IBM SDKs installed (2.9).

Thanks,

Matt

|||

I am having the same problem. Is there a way to get SQL server 2005 to talk to the Informix 7.3 engine? I get the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Ifxoledbc" for linked server "MINNYME2" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "MINNYME2".

|||

You are in luck. I'm currently still looking into this but have done a significant grind and configured the connection with ODBC. How are you connecting? With the Informix OLEDB? I'm currently waiting for the AIX admin to set something up on the server. He has to run a few scripts to set up the OLEDB properly. I found a page on IBM's site with significant documentation. Please see below. I believe I know what is wrong with my OLEDB connection, it is the set-up. I have a working ODBC now and can help you with that if need be.

http://www-306.ibm.com/software/data/informix/pubs/library/csdk_29.html

Regards,

Matt

|||

Yes, I am connecting using Informix OLE DB. I tried create a linked server with SQL Server 2005 with the IBM Informix OLE DB Provider. Here is my connection info. Did specify something wrong? It seems you abandoned this approach. Should I do the same and try to link the server with ODBC?

/****** Object: LinkedServer [MINNYME5] Script Date: 05/02/2006 11:10:06 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'MINNYME5', @.srvproduct=N'Informix-CLI 2.9 (32 bit)', @.provider=N'Ifxoledbc', @.datasrc=N'bsco@.becton_tcp', @.provstr=N'server=bsco@.becton_tcp;database=bsco;uid=informix;password=tewwetca'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'use remote collation', @.optvalue=N'true'

|||

Here is the code to set up the ODBC linked server. Please read the comments and notes provided throughout. I can help you further if need be. Just so we are on the same page, I'm using

OS: Windows XP Pro.

DotNet Framework: .NET SDK 1.1

IBM software development kit version: IBM CSDK 2.9

Good Luck!

EXEC sp_addlinkedserver

@.server = 'yourServer', -- defined in SetNet32 on tab 'Server information', -- field 'Informix Server'

@.provider = 'MSDASQL', -- DO NOT CHANGE !

@.datasrc = 'yourOdbcConnectionName', -- name of the ODBC connection defined in your system. (you must configure this.)

@.srvproduct = 'Informix-CLI 3.30 (32 bit)',

@.provstr = 'Host=yourHost;Server=yourServer;Service=yourService;Protocol=olsoctcp;Database=yourDB;UID=yourUIDToConnectToAIX;PWD=yourPWD'

NOTE: I'm not 100% sure if a login is needed but i created one.

EXEC sp_addlinkedsrvlogin 'yourLinkedServer', 'false', 'yourWindowsXPUserID', 'yourUIDToConnectToAIX', 'yourPassword'

READ-You need to set up permission for the UserId connecting to the AIX server in the AIX server. I had a AIX admin set me up considering I know little about IBM products.

Run the following query from SQL SERVER to query an ODBC linked server:

SELECT * FROM OPENQUERY(yourLinkedServer, 'select * from yourTable')

|||i done that , but cant insert into linked server tables and this error happens
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
do u know why?

|||

Although I have never tried inserting a row into an Informix Linked Server, I'd assume that it is not possible considering I've seen similar results with other data sources.

|||thank you for replying
also i cant set linked server with oledb informix
when setup linked server with odbc can update, delete and select from linked table but only cant insert into the table

I wrote my own configuration tool to maintain the connection strings in a SQL table

I wrote my own VB app to maintain all of my connection strings and link them to packages. I then grab them at run time and set them as variables in memory.Sorry this was suppose to be a reply not a new post.

i see a error when i want to do the connection...

Hi I want to connect at BD my problem is this? conn.ConnectionString ="Data Source=STKEND09560\DEFAULT2; Initial Catalog=Northwind;Integrated Security=True"; try { int no; conn.Open(); SqlCommand cmd1 =newSqlCommand(); cmd1.Connection = conn; cmd1.CommandText ="Select * from Region"; no = cmd1.ExecuteNonQuery(); TxtFirstname.Text = no.ToString(); } catch (Exception ex) { Console.WriteLine("{0} Exception caught", ex); } finally { conn.Close(); }\ = Unrecognized escape sequence. Why do i see this message?

do it like below

conn.ConnectionString ="Data Source=STKEND09560\\DEFAULT2; Initial Catalog=Northwind;Integrated Security=True";

OR

conn.ConnectionString = @."Data Source=STKEND09560\DEFAULT2; Initial Catalog=Northwind;Integrated Security=True";

I Need Help With The Connection String

I Need Help With The Connection String cuz that i need to connect with the database for Mobile Application and i have sqlexpress

Since you didn't post your connection string, it appears that you don't need help with it -you want someone to do it for you.

You might find a visit to www.connectionstrings.com to be useful.

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

I Need Help With The Connection String

I Need Help With The Connection String cuz that i need to connect with the database for Mobile Application and i have sqlexpress

Since you didn't post your connection string, it appears that you don't need help with it -you want someone to do it for you.

You might find a visit to www.connectionstrings.com to be useful.

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

I need help connecting Access 97 to SQL Server 2000

I have an Access 97 database that I connect to a SQL Server 2000
through ODBC. I have to manually set up the ODBC connection on each
user's machine if I want them to be able to use the application though.

Is there a way to store the connection string within the Access DB so
I don't have to touch the ODBC settings on each user's machine? Or is
there a different solution that I'm missing? There are way too many
for me to
setup. Thanks in advance.aperez@.easternbk.com wrote:
> I have an Access 97 database that I connect to a SQL Server 2000
> through ODBC. I have to manually set up the ODBC connection on each
> user's machine if I want them to be able to use the application though.
> Is there a way to store the connection string within the Access DB so
> I don't have to touch the ODBC settings on each user's machine? Or is
> there a different solution that I'm missing? There are way too many
> for me to
> setup. Thanks in advance.

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Use DSN-less connection strings. Like this (all one line):

ODBC;Driver=SQL Server;
Server=ServerName;Database=DBName;Trusted_Connecti on=Yes

Trusted_Connection=Yes means you're using Windows Logon security
(recommended).

If you have attached (linked) ODBC tables, you'll have to change the
tables' Connect property using VBA.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmmm44echKqOuFEgEQL9JACfayfFceXgmVsO6o+pKPKs8n V2D3cAoPJB
a1tvf0whesiFNyTgnQi1uhxN
=mRxB
--END PGP SIGNATURE--

I need assistance please

Error: "A connection was successfully established with the server, but an error occurred during the pre-login handshake. (provider: SSL Provider, error:0 - The certificate chain was issues by an authority that is not trusted.) (Microsoft SQL Server)

I am running SQL Server 2005 Developer ed. Windows XP SP2

Trying to connect over the internet to a SQL Server 2005 Workgroup ed. SP1 on Windows Small Business Server 2003 SP1

I have had success doing this before.

I can terminal sevice in to the box and confirm my credentials work

"Force Enycrption" has not been enabled on either the server or the client

The Certificate tab is clear under "Protocols for MSSQLServer", but the server does have certs I can see them in the "Certificate" dropdown.

Any help would be great.

John

check this thread

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

Madhu

|||

Madhu,

Thanks for the quick reply, but this is not even close. There is no encryption enabled and it is not even needed. It appears the error message I recieve though implies that it is enabled on the server. When I check the server through RDC under protocols, as stated before, "Force Encryption" is not enabled. I am stuck and not sure what I need to do from my client laptop which will allow me to register the server within my SQL Server Management Studio remotely. Like I stated before I have done this in the past already so I know it works.

Thanks,

John

|||

Madhu,

Did you have any other ideas on this issue I am receiveing, I still cannot connect. I cannot even ping the box, but can terminal service into it.

Thanks,

John

|||

John,

The error indicates that you ar forcing encryption on the client side (either through the connection string or through the global setting) and the server is using the self-signed certificate, i.e., not proper cert has been provisioned. You can work around this by doing 1 of the following:

1 - Specifying the "Trust Server Certificate" parameter on the client, http://msdn2.microsoft.com/en-us/library/ms131691.aspx

or

2 - Do not force encryption from the client.

Il-Sung.

|||

II-Sung,

Let me explain what I am doing and my settings.

Objective: I am trying to connect over the internet to a remote SQL Server in which I have been able to do in the past. I found out that the remote SQL Server has added a Verisign certificate and I do not seem to be able to connect through SQL Server Management Studio's. My objective is to setup replication between the 2 instances of SQL Server.

There is no application involved besides SQL Server Management Studios.

Client-side Settings:

When looking from the client-side (my laptop) in "SQL Server configuration manager | SQL Server 2005 Network configuration | and right-clicking on Protocols for SQL2005 | Properties" under the Flag tab, "Force encryption" is set to "NO", "hide instance" is set to "No" . When going under the Certificate tab there is no certificate selected. I have also tried it with the certificate in the dropdown as well, still no worky. I am not sure if I can go in the registry to see if it is set there and simply not updating the screen, not sure.

I am also not sure where to set the TrustServerCertificate setting within SQL Server Management Studios. I can tell you that in the "Connect to Server" dialogue "Encrypt connection" is not checked.

I have also tried to send my certificate from my laptop to the remote server and imported it into the "Trusted Root Certification Authority" and the "Trusted People". Still no worky.

Remote SQL Server:

When terminal servicing into the remote SQL Server and checking "SQL Server configuration manager | SQL Server 2005 Network configuration | and right-clicking on Protocols for MSSQLSERVER | Properties" under the Flag tab, "Force encryption" is set to "NO", "hide instance" is set to "No" . When going under the Certificate tab there is no certificate selected.

I am coming close to a deadline and not sure what to do, let me know where I am going wrong. I have taken all of your advise, not sure what else to do here, please advise.

John

|||

John,

Try taking a look at the client-side encryption settings in the SQL Server Configuration Manager. The places you described above are for the server side. In SQL Server Configuration Manager, right-click on "SQL Native Client Configuration" and make sure that "Force protocol encryption" is set to No. If it isn't, then this is your problem. I know that it's wierd to look at the native client settings since SSMS is a managed application but they both look at the same registry key for the encryption setting. Otherwise, please indicate what the value of the variable "Value" under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1 in the registry.

Il-Sung.

|||

II-Sung,

Thanks again for your assistance. After going into the client-side which you described (right-click SQL Native Client Config.) Force protocol encryption is set to "No" and Trusted Server Certificate is set to 'No". When going into the registry and looking at the Value for Flag1 it equals "0". Please advise...

John

|||

John,

My mistake. SSMS uses this registry vairable HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Encrypt. If this variable exists and is set to 1, then set try setting the variable to 0 and restart SSMS.

Il-Sung.

|||

II-Sung,

Dude, you are the man!!! That was it. Weird how the setting never read or updated the registry correctly. I appreciate you staying with this one. This was a stumper for me.

John

|||

Glad to hear that we got through the problem. Incidentally, the proper way to change the default setting for this variable is through the Client Configuration Tool, cliconfg.exe.

Il-Sung.