ODBC DSN SQL Port settings

Hello
I need to create two DSN's on one machine, both pointing to different
SQL Databases (but housed on the same remote server). One runs on the
standard 1433 port and the other on port 1075.
I have both of these setup, but I cannot use a different port in both.
If I set it to 1433 in DSN 1, DSN 2 automatically uses that port. If I
set DSN 2 to 1075, DSN 1 reverts to 1075. Is there any way to use
different ports using the same SQL ODBC driver?
LeonOK, it looks like I've fixed this.
It appears to revert to the same port number if the Server Name in the
DSN is the same. A quick look through the registry confirmed this.
I'm now using a hostname for one of them and the server IP address for
the other. Not the ideal solution, but it works|||Leon...I read your post with interest. I've got a similar situation on the
server side that you do. I need to have two databases running on the same
server with different IP ports (a vendor has an app with a hard-coded IP
port). Up to now I thought I had to add a seperate instance on the server
for it to listen to two different ports.
I know your original email was talking about the DSN settings, but how did
you configure the server side to be listening to two ports?
Thanks for any information.
Bob
"leonvd@.gmail.com" wrote:

> OK, it looks like I've fixed this.
> It appears to revert to the same port number if the Server Name in the
> DSN is the same. A quick look through the registry confirmed this.
> I'm now using a hostname for one of them and the server IP address for
> the other. Not the ideal solution, but it works
>|||To listen on multiple ports, use the Server Network Utility
and enter the port numbers, separated by commas in the
TCP/IP properties. You need to restart the service for the
settings to take effect.
-Sue
On Wed, 23 Aug 2006 07:39:01 -0700, RL Coppedge
<RLCoppedge@.hotmail.com.(nospam)> wrote:
[vbcol=seagreen]
>Leon...I read your post with interest. I've got a similar situation on the
>server side that you do. I need to have two databases running on the same
>server with different IP ports (a vendor has an app with a hard-coded IP
>port). Up to now I thought I had to add a seperate instance on the server
>for it to listen to two different ports.
>I know your original email was talking about the DSN settings, but how did
>you configure the server side to be listening to two ports?
>Thanks for any information.
>Bob
>
>"leonvd@.gmail.com" wrote:
>|||Cool! Thanks for the fast reply!
Bob
"Sue Hoegemeier" wrote:

> To listen on multiple ports, use the Server Network Utility
> and enter the port numbers, separated by commas in the
> TCP/IP properties. You need to restart the service for the
> settings to take effect.
> -Sue
> On Wed, 23 Aug 2006 07:39:01 -0700, RL Coppedge
> <RLCoppedge@.hotmail.com.(nospam)> wrote:
>
>

ODBC DSN on Cluster Server

Is MDAC cluster-aware?

I have an active/passive MS Cluster server.

ODBC DSN changes on the virtual cluster server are not propagated to the passive node.

How do I configure ODBC DSN such that they are propagated to both nodes in an active/passive cluster?

tia

Please note that MDAC is client driver while virtual cluster server is the server. Your ODBC DSN configuration is for the client, not for the server. The DSN will be used by the client to collect server information. So, what you need to do is to configure DSN in all client machines and the server name in the DSN shoule be the name of the SQL Server Virtual Server.

Or in short, MDAC itself is not cluster-aware.

|||

For clarification,

these ODBC DSNs I am referring to, are created on the SQL Server machine. They are used for Linked Servers within SQL Server.

So, my question is, since ODBC DSNs cannot be specified as Cluster resources how do I ensure that all DSN changes are effected on both active/passive nodes in the cluster?

|||As I said, you have to create same DSNs on both active/passive nodes.sql

ODBC DSN for SQL Server via script

Hi
I have the below script to create DSN for SQL Server. The problem is this
does not include a username and password option for SQL Server
authentication. Could someone please guide me how to add the username and
password option for authentication to this script?
Many Thanks
Regards
= Script Below ========================
Const HKEY_LOCAL_MACHINE = &H80000002
strComputer = "."
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = "My SQL Server"
strValue = "SQL Server"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
strValueName = "Database"
strValue = "My Database"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = "Driver"
strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = "Server"
strValue = "111.111.111.111"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
strValueName = "Trusted_Connection"
strValue = "No"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValueAFAIK you can't programmatically store the password.
http://support.microsoft.com/kb/184608
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"John" wrote:
> Hi
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
> Many Thanks
> Regards
>
> = Script Below ========================> Const HKEY_LOCAL_MACHINE = &H80000002
> strComputer = "."
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>|||To add to Dave's response, the SQL Server ODBC driver does not store the SQL
password for security reasons; anyone with access to the DSN could then
connect to SQL Server. The password needs to be provided by the application
when connecting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23lObOfmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
> Many Thanks
> Regards
>
> = Script Below ========================> Const HKEY_LOCAL_MACHINE = &H80000002
> strComputer = "."
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>|||what are you doing?
SQL linked tables in a MDB?
you should jsut use Access Data Projects, kid
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23lObOfmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
> Many Thanks
> Regards
>
> = Script Below ========================> Const HKEY_LOCAL_MACHINE = &H80000002
> strComputer = "."
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>|||Dan
crap answers like that is why SQL Server is _STILL_ not secure.
We don't friggin care if you think that it's secure
We don't friggin care what your premise is.
It's goddamn ridiculous that MS refuses to take SQL authentication
seriously.
I GOT CANNED AND BLACKLISTED FROM MICROSOFT FOR COMPLAINING ABOUT SQL
AUTHENTICATION
We just want to create the passwords and use SQL Authentication
I've worked in 100 different SQL Server environments these past 10 years..
and 80% of the clients in the real world use SQL Authentication for
_SOMETHING_.
stfu and take your premise back to MS and tell them to stfu and fix SQL
Authentication
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:418F29B3-9C30-406D-80A5-6B4F1AE9758F@.microsoft.com...
> To add to Dave's response, the SQL Server ODBC driver does not store the
SQL
> password for security reasons; anyone with access to the DSN could then
> connect to SQL Server. The password needs to be provided by the
application
> when connecting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23lObOfmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I have the below script to create DSN for SQL Server. The problem is
this
> > does not include a username and password option for SQL Server
> > authentication. Could someone please guide me how to add the username
and
> > password option for authentication to this script?
> >
> > Many Thanks
> >
> > Regards
> >
> >
> > = Script Below ========================> >
> > Const HKEY_LOCAL_MACHINE = &H80000002
> >
> > strComputer = "."
> >
> > Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> > strComputer & "\root\default:StdRegProv")
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> > strValueName = "My SQL Server"
> > strValue = "SQL Server"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> >
> > objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> >
> > strValueName = "Database"
> > strValue = "My Database"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strValueName = "Driver"
> > strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strValueName = "Server"
> > strValue = "111.111.111.111"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> > strValueName = "Trusted_Connection"
> > strValue = "No"
> > objReg.SetStringValue
HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
> >
> >
>

ODBC DSN for SQL Database

Hi,

I am trying to configure DSN for a remote SQL database using Windows authentication. When I logn with a user ID and configure the DSN, it's working fine. But when I logout and login with another user ID, DSN test is failing with the following message.

Connection Failed:
SQL state 28000
SQL Server Error 18456
Microsoft ODBC SQLServer Driver SQL Server Login failed
for user.

Can any one please let me know how can I configure DSN so that it should work irrespective of what domain account I use to logon to the server later?

Thanks in advance.

Srini.Hi Srini

It sounds like only the first windows account (User ID) has access to SQLServer?

You can also test connectivity by trying to connect using one of the command line tools. I like to try this with SQLCmd (for SQL Server 2005) or osql (for SQL Server 2000)

Try either

sqlcmd -S<your server name here> -E
or
osql -S<your server name here> -E

The -E option tells the tools to use Windows Authentication.

Regards

Steve|||

Hello Steve my name is Chris and i have the same problem with the SQL that Srini has had but when i tried to run the "osql -S <My Server> -E i get the error Cannot generate SSPI context message please help me and point me in the right direction.

Thanks

Chris....

ODBC DSN for SQL Database

Hi,

I am trying to configure DSN for a remote SQL database using Windows authentication. When I logn with a user ID and configure the DSN, it's working fine. But when I logout and login with another user ID, DSN test is failing with the following message.

Connection Failed:
SQL state 28000
SQL Server Error 18456
Microsoft ODBC SQLServer Driver SQL Server Login failed
for user.

Can any one please let me know how can I configure DSN so that it should work irrespective of what domain account I use to logon to the server later?

Thanks in advance.

Srini.Hi Srini

It sounds like only the first windows account (User ID) has access to SQLServer?

You can also test connectivity by trying to connect using one of the command line tools. I like to try this with SQLCmd (for SQL Server 2005) or osql (for SQL Server 2000)

Try either

sqlcmd -S<your server name here> -E
or
osql -S<your server name here> -E

The -E option tells the tools to use Windows Authentication.

Regards

Steve|||

Hello Steve my name is Chris and i have the same problem with the SQL that Srini has had but when i tried to run the "osql -S <My Server> -E i get the error Cannot generate SSPI context message please help me and point me in the right direction.

Thanks

Chris....

ODBC DSN for SQL Database

Hi,

I am trying to configure DSN for a remote SQL database using Windows authentication. When I logn with a user ID and configure the DSN, it's working fine. But when I logout and login with another user ID, DSN test is failing with the following message.

Connection Failed:
SQL state 28000
SQL Server Error 18456
Microsoft ODBC SQLServer Driver SQL Server Login failed
for user.

Can any one please let me know how can I configure DSN so that it should work irrespective of what domain account I use to logon to the server later?

Thanks in advance.

Srini.Hi Srini

It sounds like only the first windows account (User ID) has access to SQLServer?

You can also test connectivity by trying to connect using one of the command line tools. I like to try this with SQLCmd (for SQL Server 2005) or osql (for SQL Server 2000)

Try either

sqlcmd -S<your server name here> -E
or
osql -S<your server name here> -E

The -E option tells the tools to use Windows Authentication.

Regards

Steve|||

Hello Steve my name is Chris and i have the same problem with the SQL that Srini has had but when i tried to run the "osql -S <My Server> -E i get the error Cannot generate SSPI context message please help me and point me in the right direction.

Thanks

Chris....

ODBC DSN ....

Hello .
im programing a one application
in Delphi . and my program connected to
sql server successfully . but thats not why
im calling . suppose i exported my program ( .exe )
and sell it . as you know in users pc DSN dosnt
exist in odbc . can they open my program without
DSN in their systems ? or i must create DSN File
and export it by Setup in Users pc ?
Help me please . thanks for your worksYou can use dsn-less connections or you could
programmatically create the needed DSN as part of your
setup/install package. I'm not sure how to code it in Delphi
but the following are VB examples that might help you get
started:
HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
-Sue
On Thu, 4 Dec 2003 01:39:50 -0800, "Toxi_boy"
<stanboy733@.hotmail.com> wrote:

quote:

---

>Hello .
>im programing a one application
>in Delphi . and my program connected to
>sql server successfully . but thats not why
>im calling . suppose i exported my program ( .exe )
>and sell it . as you know in users pc DSN dosnt
>exist in odbc . can they open my program without
>DSN in their systems ? or i must create DSN File
>and export it by Setup in Users pc ?
>Help me please . thanks for your works

---

sql

ODBC Drivers error '80004005'

Hello, I am running IIS 6.0 on a windows 2003 server. I have an asp page
that accessed a SQL server 2000 running on windows 2003 through DSN. Every
once in a while the webpage becomes inaccessable and gives the following
message:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNETLIB] General network error.
Check your network documentation.
/direct/dbConn.asp, line 34
Line 34 of the asp file contains the line:
adoCon.Open "dsn=DatabaseConnect;uid=login;pwd=test"
The webpage doesn't just comeback after a few minutes but instead stays
inaccessible until the SQL Server is restarted.
The only way around this problem has been to restart the SQL server every 3
hours in order to decrease the webserver downtime.
Please help,
Danny
It looks like an upgrade to SQL Server 2000 Service Pack 4 solved the
problem.
Dannyb
"Dannyb" <dannyb@.alianza.com> wrote in message
news:uPpKjQqNGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hello, I am running IIS 6.0 on a windows 2003 server. I have an asp page
> that accessed a SQL server 2000 running on windows 2003 through DSN.
> Every
> once in a while the webpage becomes inaccessable and gives the following
> message:
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][DBNETLIB] General network error.
> Check your network documentation.
> /direct/dbConn.asp, line 34
> Line 34 of the asp file contains the line:
> adoCon.Open "dsn=DatabaseConnect;uid=login;pwd=test"
> The webpage doesn't just comeback after a few minutes but instead stays
> inaccessible until the SQL Server is restarted.
> The only way around this problem has been to restart the SQL server every
> 3
> hours in order to decrease the webserver downtime.
> Please help,
> Danny
>
>

ODBC Drivers error '80004005'

Hello, I am running IIS 6.0 on a windows 2003 server. I have an asp page
that accessed a SQL server 2000 running on windows 2003 through DSN. Every
once in a while the webpage becomes inaccessable and gives the following
message:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNETLIB] General network er
ror.
Check your network documentation.
/direct/dbConn.asp, line 34
Line 34 of the asp file contains the line:
adoCon.Open "dsn=DatabaseConnect;uid=login;pwd=test"
The webpage doesn't just comeback after a few minutes but instead stays
inaccessible until the SQL Server is restarted.
The only way around this problem has been to restart the SQL server every 3
hours in order to decrease the webserver downtime.
Please help,
DannyIt looks like an upgrade to SQL Server 2000 Service Pack 4 solved the
problem.
Dannyb
"Dannyb" <dannyb@.alianza.com> wrote in message
news:uPpKjQqNGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hello, I am running IIS 6.0 on a windows 2003 server. I have an asp page
> that accessed a SQL server 2000 running on windows 2003 through DSN.
> Every
> once in a while the webpage becomes inaccessable and gives the following
> message:
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][DBNETLIB] General netwo
rk error.
> Check your network documentation.
> /direct/dbConn.asp, line 34
> Line 34 of the asp file contains the line:
> adoCon.Open "dsn=DatabaseConnect;uid=login;pwd=test"
> The webpage doesn't just comeback after a few minutes but instead stays
> inaccessible until the SQL Server is restarted.
> The only way around this problem has been to restart the SQL server every
> 3
> hours in order to decrease the webserver downtime.
> Please help,
> Danny
>
>

ODBC drivers

Hi
On our SQL 2000 Std server, when creating a ODBC there are no drivers to
chose from (i.e SQL DSN etc). Even when installing addtional SQL instance
we get a Cannot find default Data Source message)
I have tried reinstalling MDAC 2.7 but the ODBC drivers does not appear in
the drivers tabs in ODBC admin tool.
I am stuck to why this is happening. Any help/advice to resolve this
strange problem.
p.s i can use Enterprise Manager from my working station to connect to the
SQL server ( but not on the server itself)
Regards
James DownesI have solved the problem. I imported the ODBC registry keys from a working
ODBC server.
Regards
Z
"Zenduce" <james@.zenduce.net> wrote in message
news:#EFueg4SDHA.1916@.TK2MSFTNGP12.phx.gbl...
> Hi
> On our SQL 2000 Std server, when creating a ODBC there are no drivers to
> chose from (i.e SQL DSN etc). Even when installing addtional SQL
instance
> we get a Cannot find default Data Source message)
> I have tried reinstalling MDAC 2.7 but the ODBC drivers does not appear
in
> the drivers tabs in ODBC admin tool.
> I am stuck to why this is happening. Any help/advice to resolve this
> strange problem.
> p.s i can use Enterprise Manager from my working station to connect to
the
> SQL server ( but not on the server itself)
> Regards
> James Downes
>sql

ODBC Data Source Administrator

Hi all, hope you can help?
I have created a system DSN in the usual way using the above ODBC tool
(I am trying to create a connection to our Open Accounts package using
the OpenEdge 10.1A driver). I provide the host name, the port number
and the database name, the user ID and password and test the
connection. All works a treat...'Connection established'.
I open up Excel and try to import data using my newly created DSN...it
exposes all of the tables within the database and lets me select
fields from those table. When I get to the 'return data to Microsoft
Excel' part I get the following error message 'Access denied
(Authorisation failed) (7512)'.
Any ideas guys?
Regards
PaulHi Paul,
What database is the Open Accounts package using to store it's data, SQL
Server, Access, Oracle etc. We need to know this because we need to
troubleshoot the security settings/accounts etc. of the database
containing the Open Accounts data.
Jonathan
thePriest wrote:
> Hi all, hope you can help?
> I have created a system DSN in the usual way using the above ODBC tool
> (I am trying to create a connection to our Open Accounts package using
> the OpenEdge 10.1A driver). I provide the host name, the port number
> and the database name, the user ID and password and test the
> connection. All works a treat...'Connection established'.
> I open up Excel and try to import data using my newly created DSN...it
> exposes all of the tables within the database and lets me select
> fields from those table. When I get to the 'return data to Microsoft
> Excel' part I get the following error message 'Access denied
> (Authorisation failed) (7512)'.
> Any ideas guys?
> Regards
> Paul
>

ODBC Data Source

I am new to SSIS packages and want to use an ODBC data source connected to an old Btrieve set of data files. I set up the ODBC DSN, and my SQL Server Data Connection, and can browse the tables in the Server Explorer. However with regards to my SSIS package, I'm not sure whether to use an OLE DB Source or Data Reader Source. I have futzed with both of them but haven't had any luck at seeing the tables. Can someone please give me some tips.

Thanks,
Carson

Carson,

Use the OLE DB Source. That's generally regarded as the default option and, I think, will be quicker than datareader source.

-Jamie

|||

Then this is my issue with the OLE DB Source. I go into the OLE DB Source Editor, nothing is listed in my OLE DB Connection Manager dropdown, so I click the "New..." button.

The Configure OLE DB Connection Manager window pops up, but nothing is listed under Data Connections, the listbox is blank. So I click the "New..." button on this popup window. (Keep in mind, in my Server Explorer I have two Data Connections my localhost and the ODBC DSN i explained in my first post. Also, in the bottom of the package, under the tab Connection Managers I also have defined an ODBC DSN connection by right clicking and selecting New Connection and then ODBC.)

Then up comes the Connection Manager popup, yet ODBC is not in the drop-down list of Providers like it was when I created a Connection Manager by right-clicking in the section Connection Manager found underneath the package DataFlow.

I do not have an OLE driver for this old Btrieve data source, only a working ODBC driver.

Please advise.

Thanks!

|||

Carson,

The only way to use ODBC connections in SSIS is by using ADO .NET provider for ODBC. That implies you have to use Data Reader source with it. There is no a custom UI for the Data Reader source component so you would need to type your query (select * from <tableName> or equivalent one) directly into the SQLCommand property. You will not be able to see available tables.

HTH.

|||

Thank you for this explanation, that helps. I'm assuming that before I can set the SQLCommand property, I have to set the Connection Manager in the first tab of the Advanced Editor. When I do, I get the following error: "Error at Data Flow Task [DataReader Sourcer [175]]: Cannot acquire a managed connection from the run-time connection manager." Can someone please explain what to do with this error?

I created the connection manager for my ODBC data source by right-clicking in the area directly under the package data flow UI, and selecting "New Connection", then selecting "ODBC", then I create a new connection manager by setting the system data source name to the ODBC DSN that I created. The same DSN I can browse the tables with in the Server Explorer.

Please advise.

Thanks!!

|||

You have to choose "New ADO .NET Connection..." from the menu, click on the "New..." button of the next dialog, select .NET Providers/Odbc Data Provider in the first drop-down of the following dialog and then choose your DSN.

This way created connection can now be used in the advanced UI of the Data Reader Source.

Thanks.

|||Bob - You 'Da Man!! That worked!|||

Bob, where is the option for new ado .net connection?

Thanks

|||

Right-click in the "Connection Managers" window and then follow my previous post.

HTH.

ODBC Data Source

I am new to SSIS packages and want to use an ODBC data source connected to an old Btrieve set of data files. I set up the ODBC DSN, and my SQL Server Data Connection, and can browse the tables in the Server Explorer. However with regards to my SSIS package, I'm not sure whether to use an OLE DB Source or Data Reader Source. I have futzed with both of them but haven't had any luck at seeing the tables. Can someone please give me some tips.

Thanks,
Carson

Carson,

Use the OLE DB Source. That's generally regarded as the default option and, I think, will be quicker than datareader source.

-Jamie

|||

Then this is my issue with the OLE DB Source. I go into the OLE DB Source Editor, nothing is listed in my OLE DB Connection Manager dropdown, so I click the "New..." button.

The Configure OLE DB Connection Manager window pops up, but nothing is listed under Data Connections, the listbox is blank. So I click the "New..." button on this popup window. (Keep in mind, in my Server Explorer I have two Data Connections my localhost and the ODBC DSN i explained in my first post. Also, in the bottom of the package, under the tab Connection Managers I also have defined an ODBC DSN connection by right clicking and selecting New Connection and then ODBC.)

Then up comes the Connection Manager popup, yet ODBC is not in the drop-down list of Providers like it was when I created a Connection Manager by right-clicking in the section Connection Manager found underneath the package DataFlow.

I do not have an OLE driver for this old Btrieve data source, only a working ODBC driver.

Please advise.

Thanks!

|||

Carson,

The only way to use ODBC connections in SSIS is by using ADO .NET provider for ODBC. That implies you have to use Data Reader source with it. There is no a custom UI for the Data Reader source component so you would need to type your query (select * from <tableName> or equivalent one) directly into the SQLCommand property. You will not be able to see available tables.

HTH.

|||

Thank you for this explanation, that helps. I'm assuming that before I can set the SQLCommand property, I have to set the Connection Manager in the first tab of the Advanced Editor. When I do, I get the following error: "Error at Data Flow Task [DataReader Sourcer [175]]: Cannot acquire a managed connection from the run-time connection manager." Can someone please explain what to do with this error?

I created the connection manager for my ODBC data source by right-clicking in the area directly under the package data flow UI, and selecting "New Connection", then selecting "ODBC", then I create a new connection manager by setting the system data source name to the ODBC DSN that I created. The same DSN I can browse the tables with in the Server Explorer.

Please advise.

Thanks!!

|||

You have to choose "New ADO .NET Connection..." from the menu, click on the "New..." button of the next dialog, select .NET Providers/Odbc Data Provider in the first drop-down of the following dialog and then choose your DSN.

This way created connection can now be used in the advanced UI of the Data Reader Source.

Thanks.

|||Bob - You 'Da Man!! That worked!|||

Bob, where is the option for new ado .net connection?

Thanks

|||

Right-click in the "Connection Managers" window and then follow my previous post.

HTH.

ODBC Create a new Data Souce to SQL not displaying

On a Windows XP PC, why doesn't the Create a new Data
Source to SQL Server window pop up when you select to add
a new system dsn from the ODBC data source
administrator? I have one particular PC where this won't
come up, not all XP PCs though. Anyone see this before!
Thanks.
.Did you try reinstalling MDAC? You'd want to at least check
the MDAC configuration on the PC using component checker.
You can download the latest MDAC as well as component
checker from:
http://msdn.microsoft.com/downloads/list/dataaccess.asp
-Sue
On Mon, 1 Dec 2003 08:06:15 -0800, "Nil"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>On a Windows XP PC, why doesn't the Create a new Data
>Source to SQL Server window pop up when you select to add
>a new system dsn from the ODBC data source
>administrator? I have one particular PC where this won't
>come up, not all XP PCs though. Anyone see this before!
>Thanks.
>.
>

---

ODBC Connectivity problem in Sql Server2005

Hi,

I have created DSN connection with sql server authentication in Sql server 2005. There is message comes "Connection Succeeded".

After I try through my application again asking username and password for the DSN at run time. My application is developed in Powerbuilder 5.0.

The connection string as follows,

SQLCA.dbms= "ODBC"
SQLCA.dBparm = "CONNECTSTRING='DSN=ntm',APPNAME = 'ntm',uid='sa',pwd='sierra'"

Can you help me?

I'm not really sure what Powerbuilder syntax is, but did you try something like:
SQLCA.dBparm ="CONNECTSTRING='DSN=ntm;uid=sa;pwd=sierra'"|||

Thanks for your kind reply..

We have rectified that problem.

sql

ODBC Connectivity problem in Sql Server2005

Hi,

I have created DSN connection with sql server authentication in Sql server 2005. There is message comes "Connection Succeeded".

After I try through my application again asking username and password for the DSN at run time. My application is developed in Powerbuilder 5.0.

The connection string as follows,

SQLCA.dbms= "ODBC"
SQLCA.dBparm = "CONNECTSTRING='DSN=ntm',APPNAME = 'ntm',uid='sa',pwd='sierra'"

Can you help me?

I'm not really sure what Powerbuilder syntax is, but did you try something like:
SQLCA.dBparm ="CONNECTSTRING='DSN=ntm;uid=sa;pwd=sierra'"|||

Thanks for your kind reply..

We have rectified that problem.

ODBC Connection to SQL Server 2005sp2 Installed on VISTA.

Hi people i am getting a problem when im creating a dsn to sonnect the SQL server as the database for my content server.....................i tried all the tricks given by you people but its still not workin it is showins a message that .............

Connection failed.

SQL State'01000'

[Microsoft][ ODBC SQL Server Driver][Named Pipes]Connection Open

(Connect()).

Connection Failed:

SQL State:'08001'

SQL Server Error:17

[Microsoft ][ODBC SQL Server Driver ][Named Pipes]SQL Server doe not exists or access denied............................

Please any body can help me out with this problem..............?

--
Chetan

Hi Chetan,

This seems like a typical connectivity problem probably not related to Vista. Please follow the instructions for posting a connectivity question from the following links:

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

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

HTH,

Jivko Dobrev - MSFT

--

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

ODBC Connection to a linked database

I'd like to set up an ODBC DSN to a table in a linked SQL Server, via
my local SQL Server.

I'm having a few problems:
1. When I use Enterprise Manager to link the remote SQL Server, it
doesn't allow me to select the database in the remote server. It only
shows one database.

2. When I use the Data Sources program to set up the ODBC DSN, it only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.Thats quite normal, but you are able to use the four part notation,
which will switch to another database if specified.

E.g. ODBC Connection points automatically to Northwind ( as the default
database of the user) you can use

SELECT <columnlist> FROM Linkdservername.Databasename.Objectname

which switches to the other database to select.

HTH, Jens Suessmeyer.|||Jens,

Thank you.

I'd rather not need to change the table names in the MDB. They just
use the default database of the ODBC Data Source. How can I set the
ODBC default database to be a database in a linked server?|||if you are using a DSN, just go in the ODBC Administrator (of Windows)
and change the database to the ones needed.

HTH, jens Suessmeyer.|||Jens,

Thanks - but the ODBC Adminsitrator only shows *local* databases, not
databases on the linked server. If I try to just type it in, it tells
me that it's not a valid db, and refuses to let me do so!!!|||How did you register the linked server ? Is it a DSN bind linked server
or was it specified within a conneciton string ? ODBC Administrator
doesnt show only the local databases, it shows the servers/databases
that were specified within the DSN. Look at the server ODBC Admin to
see if connection to your linked server is specified (some people look
on their local DSN rather than looking on the remote DSNs onthe SQL
Server which uses the linked server)

HTH, Jens Suessmeyer.

ODBC connection frequently drops in routed network

Hello,
we are having serious problems with an application that uses an ODBC
connection (System DSN) to a SQL Server in another subnet. The network is
fine, all network tests reveal nothing of consequence. However, the problem
does not appear when SQL server and client are in the same subnet.
The ODBC connection from the client gets dropped at seemingly random
intervals and in the logs one can see the following:
{NIL, 10054, [01000][10054][Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionWrite (send()).}
{NIL, 59, [01000][59][Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionWrite (WrapperWrite()).}
{NIL, -1, [08S01][0][Microsoft][ODBC SQL Server
Driver]Communication link failure}
We honestly don't know what else to look for. Maybe anyone has an idea?
Thanks in advance,
Thomas Liss
Well, after configuring "Flow Control" on the network cards of the SQL
Server, the problems were gone.
"Thomas Liss" <liss@.uipugmbh.com> wrote in message
news:upFsIo9TFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Hello,
> we are having serious problems with an application that uses an ODBC
> connection (System DSN) to a SQL Server in another subnet. The network is
> fine, all network tests reveal nothing of consequence. However, the
> problem does not appear when SQL server and client are in the same subnet.
> The ODBC connection from the client gets dropped at seemingly random
> intervals and in the logs one can see the following:
> {NIL, 10054, [01000][10054][Microsoft][ODBC SQL Server
> Driver][DBNETLIB]ConnectionWrite (send()).}
> {NIL, 59, [01000][59][Microsoft][ODBC SQL Server
> Driver][DBNETLIB]ConnectionWrite (WrapperWrite()).}
> {NIL, -1, [08S01][0][Microsoft][ODBC SQL Server
> Driver]Communication link failure}
> We honestly don't know what else to look for. Maybe anyone has an idea?
>
> Thanks in advance,
>
> Thomas Liss
>
|||Could you please explain what was the old setting and what is the new one?
Regards,
Marko Erzen
"Thomas Liss" <liss@.uipugmbh.com> wrote in message
news:e2wez$JUFHA.2172@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Well, after configuring "Flow Control" on the network cards of the SQL
> Server, the problems were gone.
>
> "Thomas Liss" <liss@.uipugmbh.com> wrote in message
> news:upFsIo9TFHA.2664@.TK2MSFTNGP15.phx.gbl...
is[vbcol=seagreen]
subnet.
>
|||Thomas,
I just read your post and we are experiencing the same kind of error
here. The communication link failure is only occurring for clients
connecting from outside of the server's home subnet and the failure is
intermittent. Did you ever resolve your problems?
*** Sent via Developersdex http://www.codecomments.com ***

ODBC connection frequently drops in routed network

Hello,
we are having serious problems with an application that uses an ODBC
connection (System DSN) to a SQL Server in another subnet. The network is
fine, all network tests reveal nothing of consequence. However, the problem
does not appear when SQL server and client are in the same subnet.
The ODBC connection from the client gets dropped at seemingly random
intervals and in the logs one can see the following:
{NIL, 10054, [01000][10054][Microsoft][ODBC SQL Se
rver
Driver][DBNETLIB]ConnectionWrite (send()).}
{NIL, 59, [01000][59][Microsoft][ODBC SQL Serve
r
Driver][DBNETLIB]ConnectionWrite (WrapperWrite()).}
{NIL, -1, [08S01][0][Microsoft][ODBC SQL Server
Driver]Communication link failure}
We honestly don't know what else to look for. Maybe anyone has an idea?
Thanks in advance,
Thomas LissWell, after configuring "Flow Control" on the network cards of the SQL
Server, the problems were gone.
"Thomas Liss" <liss@.uipugmbh.com> wrote in message
news:upFsIo9TFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Hello,
> we are having serious problems with an application that uses an ODBC
> connection (System DSN) to a SQL Server in another subnet. The network is
> fine, all network tests reveal nothing of consequence. However, the
> problem does not appear when SQL server and client are in the same subnet.
> The ODBC connection from the client gets dropped at seemingly random
> intervals and in the logs one can see the following:
> {NIL, 10054, [01000][10054][Microsoft][ODBC SQL
Server
> Driver][DBNETLIB]ConnectionWrite (send()).}
> {NIL, 59, [01000][59][Microsoft][ODBC SQL Ser
ver
> Driver][DBNETLIB]ConnectionWrite (WrapperWrite()).}
> {NIL, -1, [08S01][0][Microsoft][ODBC SQL Serv
er
> Driver]Communication link failure}
> We honestly don't know what else to look for. Maybe anyone has an idea?
>
> Thanks in advance,
>
> Thomas Liss
>|||Could you please explain what was the old setting and what is the new one?
Regards,
Marko Erzen
"Thomas Liss" <liss@.uipugmbh.com> wrote in message
news:e2wez$JUFHA.2172@.TK2MSFTNGP15.phx.gbl...
> Well, after configuring "Flow Control" on the network cards of the SQL
> Server, the problems were gone.
>
> "Thomas Liss" <liss@.uipugmbh.com> wrote in message
> news:upFsIo9TFHA.2664@.TK2MSFTNGP15.phx.gbl...
is[vbcol=seagreen]
subnet.[vbcol=seagreen]
>|||Thomas,
I just read your post and we are experiencing the same kind of error
here. The communication link failure is only occurring for clients
connecting from outside of the server's home subnet and the failure is
intermittent. Did you ever resolve your problems?
*** Sent via Developersdex http://www.codecomments.com ***sql