OLAP connection problems

I have two systems setup with SQL Server 2000 + Analysis Services. Both servers have been patched with both SP3a patches.

I wrote a trivial command line app that tests connection strings through ADO.

On each system, I can connect locally perfectly using the connection string:
"Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

However, once I change "Data Source" to refer to the other system, the call to ADODB.Connection.Open throws a "System.Runtime.InteropServices.COMException" with message: "Unspecified error". (The simple app is written in C# and talks to ADO through Interop)

I've tried referencing ADO 2.1, ADO 2.5 and ADO 2.7
I've tried a "Provider" of "MSOLAP.2" as well as just "MSOLAP"
I've also tried a variety of different connection strings with many different permissions settings.

It is very frustrating to get an absolutely generic error message.

Any ideas?

Thanks in advance!Have you tried adding "User ID=xxx;Password=xxx"

or

"Integrated Security=SSPI"|||Yes, just retried all of them and they all result in the same System.Runtime.InteropServices.COMException, "Unspecified error"

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"|||Let me reclarify. This one works fine:

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

These (that reference another system) fail with System.Runtime.InteropServices.COMException, "Unspecified error":

adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"

I experience the same effect from two systems; either system can open its own database but can't open a remote one.|||Can't think of anything here...Maybe these links can give you some ideas?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_8wfm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_64fb.asp

OLAP Connection in c#

Hello,

I'm trying to connect to my OLAP Cube with a c# project.

This is my code:

private void Page_Load(object sender, System.EventArgs e)

{

ADODB.ConnectionClass conn=new ConnectionClass();

conn.Open(ConfigurationSettings.AppSettings["connection_string"].ToString(),ConfigurationSettings.AppSettings["user_conn"].ToString(),ConfigurationSettings.AppSettings["password_conn"].ToString(),0);

try

{ADOMD.CellsetClass cs=new CellsetClass();

cs.ActiveConnection=conn;

string szMDX="";

szMDX += "SELECT ";

szMDX += "{[Measures].members} ON COLUMNS,";

szMDX += "[CAMPAGNE].[Dim CAMPAGNE].members ON ROWS";

szMDX += " FROM [My Roi]";

cs.Open(szMDX,conn); // here there is an error

}

I've receive this error message:

"Impossible to use this connexion to make this operation, the connexion is closed or invalid in this context".

Can you help me please, because I can't find the problem.

Thanks,

Have you tried changing the provider in the connection string to MSOLAP.1 or MSOLAP.2 and did you verify that either TRUSTED_CONNECTION=YES or INTEGRATED SECURITY=SSPI is included?

OLAP Connection error

Below code at web.config file, when i change Data Source to other pc name....an error occur 'The data provider didn't supply any further error information'...
let said change the datasource 'localhost' to 'webserver' error will occur...so i cannot run the program at my pc and link the olap database at server...

<add key="OLAPConnectionString" value="Provider=MSOLAP.2;Data Source=localhost;Initial Catalog=Foodmart 2000;" />The other PC that you are trying to connect should have the same users and user rights as the first one.

In addition to that, the Analysis Server on your second PC should know about these users as well.

I found that security with Analysis Server is very tight.

Good Luck

OLAP Connection Error

Hi,
Background :
Am trying to connect to the OLAP Cubes from a Web Service.
The Web Service resides on my local machine, while the Analysis Server resid
es on a different domain.
Users have been created on the Analysis Server, and roles have been defined
for the cube am trying to access.
The processModel is configured with proper user id.
Problem :
When I do that I get an error "Undefined Error".
To my knowledge I have all the access to the Analysis Server, but still am n
ot able to connect to cube.
Could someone please help me resolve this as its very crucial and is hamperi
ng our to a great extent.
Thanks,
SrinivasThe user is created on the Analysis Server, and a role too has been defined
for the user, giving access to the cube.
For example :
Let us suppose my network user id is "ABC". Then a user called "ABC" is crea
ted on the Analysis server, and has been assigned a role.
Srinivas|||microsoft.public.sqlserver.olap is probably a better newsgroup for this, but
my initial guess is that your problem revolves around the statement below
"different domain". AS only supports Windows authentication in direct
client-server mode. You have a few options. First, you could use the old NT
matching-username password trick. If you have *local accounts* on both
systems with the same username password, then you will be able to connect
even if the two machines are in untrusted domains. You will have
administrative issues keeping the two passwords in-sync, but the connection
will go through OK. Second, if you are running on Enterprise Edition, you
can use http access. This will require you to run IIS on the AS server (see
BOL on how to configure it and look at the white paper
http://msdn.microsoft.com/library/e...ql_datapump.asp for
more technical details.
Hope that helps.
Dave Wickert [MS]
dwickert@.online.microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Srinivas" <svinjamoor@.worldbank.org> wrote in message
news:19E0A695-14C5-4E9C-8BB8-FB7C26A310BC@.microsoft.com...

quote:

---

> Hi,
> Background :
> Am trying to connect to the OLAP Cubes from a Web Service.
> The Web Service resides on my local machine, while the Analysis Server

---

resides on a different domain.

quote:

---

> Users have been created on the Analysis Server, and roles have been

---

defined for the cube am trying to access.

quote:

---

> The processModel is configured with proper user id.
> Problem :
> When I do that I get an error "Undefined Error".
> To my knowledge I have all the access to the Analysis Server, but still am

---

not able to connect to cube.

quote:

---

> Could someone please help me resolve this as its very crucial and is

---

hampering our to a great extent.

quote:

---

> Thanks,
> Srinivas

---

OLAP AS Connection Weirdness

I have a stand alone win2k3 report server(i.e. SQL is not on the box).
RS SP1 is installed
I have a SQLRS report developed in visual studio (also loaded on RS
box) that runs MDX against an analysis services cube. I am using the
OLE DB provider for OLAP services 8.0. (Note that we had to run
ptsfull.exe to get the pivot table service and 8.0 provider to be
available)
I can successfully preview the report in visual studio.
When I upload the report to Report Manger, the report bombs with...
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot create a connection to data source 'AAHP'.
(rsErrorOpeningConnection) Get Online Help
Database '<source name>' does not exist.
Am I missing something on the report server?
Thanks!Jim wrote:
> When I upload the report to Report Manger, the report bombs with...
> An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot create a connection to data source 'AAHP'.
> (rsErrorOpeningConnection) Get Online Help
> Database '<source name>' does not exist.
> Am I missing something on the report server?
The report bombs while uploading?
Do you tried to open the AS-DB in the analysis-manager on the reportserver?
regards
Frank|||Sorry, no. The report bombs when I try to run it via report manager. I
can open the AS-DB and cube in Analysis Manger (which is Not on the
report server). I have a role setup that should allow the stored
credentials to successfully authenticate.|||Jim wrote:
> Sorry, no. The report bombs when I try to run it via report manager. I
> can open the AS-DB and cube in Analysis Manger (which is Not on the
> report server). I have a role setup that should allow the stored
> credentials to successfully authenticate.
Jim,
lets have a look into the eventlog of the server where the analysis cube
resides.
Source MSSQLSERVEROLAPSERVER.
If there errors with something like "NT-Authority/Anonymous" your
security-settings on the report-server are not valid (especially the
settings in internet service manager)
If there aren't any errors it become more difficult...Do you run ptsfull on
reportserver either?
regards
Frank|||Thanks for your replies.
No errors unfortunately.
I ran ptsfull on the reportserver.
As I mentioned I can connect to the AS server via Visual Studio. As
soon as I deploy the report (using a stored connection or shared
connection) to report manager it won't run. I'm running MDAC 2.8 on the
report server.|||Jim wrote:
> As I mentioned I can connect to the AS server via Visual Studio.
Yes..i know. You can connect to the AS from YOUR workstation and your
reportserver can't as you wrote.
The problem should be between reportserver and AS-server but wich problem? I
dont know!
regards
frank|||Just to clarify...
Visual Studio - where I am developing the reports - is on the report
server.|||Jim,
I am having same problems and was wondering if you found solution of this
one. Thanks.
"Jim" wrote:
> Just to clarify...
> Visual Studio - where I am developing the reports - is on the report
> server.
>

OLAP Analysis Services - Remote Connection issue

Hi!

does anybody tell me how to connect remotely to an OLAP Analysis Server?

I have defined a local windows account on the OLAP server and added it to AS cube roles.

Despite that I still can't connect remotely to the AS server.

Thanks,

Chris

If this is about connecting with SQL Management Studio (or BI Development Studio) to Analysis Services 2005:

- you need to have a Windows account in the domain of the 2 machines (the AS2005 machine and the client machine from where you want to connect remotely)

- temporarily make the user a server administrator on the AS2005 (to eliminate the potential permission problems); you can do this with SQL Management Studio, right click on the server item -> Properties -> Security

- run SQL Management Studio on the client machine as that user ("Run As..." should be fine, no need to login to Windows as that user)

- if the connection fails, then please check the firewall(s), allow incoming connections for msmdsrv.exe (the AS2005 server)

- if the connection succeeds, then downgrade the user from server administrator; it is enough to grant read definition permission for the database intended; to do this, run SQL Management Studio from an AS2005 admin account and then add the user as a member of the database role

Adrian Dumitrascu

Ok, how do I do this ?

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Offline mode ?

Hi all,
I am designing and testing a report using an ODBC connection. The
report is short (one or two pages) but the SQL request has many tables
and takes several minutes. Being in development phase I don't need the
datas to be refreshed from SQL database every time I click on
"preview" to see the report, but this is what happens (I find no other
reason for so long response time...). So I spend a lot of time waiting
for response.
Is there a kind of "offline" mode to see layout modifications without
refreshing datas every time ?
Thanks !
Jean-MarcTry using your SQL query to export the data from the multiple tables into a
single table. For your development and testing, base your report data on the
single table.
Using Query Analyzer, do:
SELECT * INTO MyTable FROM (rest of complex query)
Now base your test report on the data in MyTable. (You can also use DTS to
create the single table, and can even export the data into a local Access
table).
Charles Kangai, MCT, MCDBA
"Jean-Marc Audrin" wrote:
> Hi all,
> I am designing and testing a report using an ODBC connection. The
> report is short (one or two pages) but the SQL request has many tables
> and takes several minutes. Being in development phase I don't need the
> datas to be refreshed from SQL database every time I click on
> "preview" to see the report, but this is what happens (I find no other
> reason for so long response time...). So I spend a lot of time waiting
> for response.
> Is there a kind of "offline" mode to see layout modifications without
> refreshing datas every time ?
> Thanks !
> Jean-Marc
>sql

Offisite DB Connection Failure

I am trying to write a simple program for test purpose with VB .Net 2005 and SQL Server Express 2005. My server is not even in the same state as me, but I have the following on the server:

Server 2003 Web Edition SP1
SQL Server Express 2005

I am writing in the following:
Visual Basic 2005 Pro Edition

I have read where you can setup Express to allow TCP/IP connection, and I have done what it stated HERE (http://www.datamasker.com/SSE2005_NetworkCfg.htm). However, I am still not able to connect to the DB on the remote server.

I go to:
Tools --> Connect to Server --> Type IP and get the connection failed to connec to server "xxx.xxx.xxx.xxx"
I even tried:
Tools --> Connect to Server --> Type IP\SQLEXPRESS and get the connection failed to connec to server "xxx.xxx.xxx.xxx"

Has anyone been able to do this, or am I wasting my time? If I can get the SQL Server to do as requested then I am going to purchase and work with it, however, I want to test out before I spend the $$$$$ to begin the full project. If anyone can be of assistance here I would appreciate it.

Thanks in advance.Can you access the server on a remote machine on the same location as the server is (that is, in the same domain, at least without going through a firewall) ? It might have something to do with TCP/IP port 1433 that is not opened in the firewall ... and believe me, you don't want that !!!
Is your SQL server directly accessible from the Internet with IP address xxx.xxx.xxx.xxx ? Or should some routing be done because it's behind a firewall ? I would check your network admin for some assistence ...

Gr,
Yveausql

Odd Unicode problem

I use Server2000 and ADO connection and Unicode.
I have fields type of nvarchar.
And when I do the search ADO creates the SQL statement like this:
exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
nvarchar(120),@.P3 smallint', 0, N'?', 7
(Caught from Profiler)
And this result nothing.
but when I delete the N from the second parameter:
..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, '?', 7 <-- no N
before the Unicode variable, it works (work - means result the correct
records)!
and to make it more odd
..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, N'John', 7 -- with N
works too.
I have tested those statements on S2k Query Analyzer and S2k5 SQL Server
Management Studio Express and there are same results.
Have anyone any idea what is wrong?
Regards,
Tomasz RutkowskiHi Tom
My guess is that your data is not what you expect
If you update the data
BEGIN TRANSACTION
UPDATE dbo.AB_EntityTreeNodeLink
SET EntityTitle = N'?'
WHERE EntityTitle = '?'
exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
nvarchar(120),@.P3 smallint', 0, N'?', 7
ROLLBACK TRANSACTION
Does it work?
John
"TomX" wrote:
> I use Server2000 and ADO connection and Unicode.
> I have fields type of nvarchar.
> And when I do the search ADO creates the SQL statement like this:
> exec sp_executesql N'SELECT * FROM dbo.AB_Entities ab_e (NOLOCK) INNER JOIN
> dbo.AB_EntityTreeNodeLink ab_etl (NOLOCK) ON
> ab_e.EntityId=ab_etl.ChildEntityId WHERE ( ab_e.EntityCluster = @.P1) AND (
> ab_e.EntityTitle = @.P2 AND ab_e.EntityType = @.P3) ', N'@.P1 int,@.P2
> nvarchar(120),@.P3 smallint', 0, N'?', 7
> (Caught from Profiler)
> And this result nothing.
> but when I delete the N from the second parameter:
> ...., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, '?', 7 <-- no N
> before the Unicode variable, it works (work - means result the correct
> records)!
> and to make it more odd
> ..., N'@.P1 int,@.P2 nvarchar(120),@.P3 smallint', 0, N'John', 7 -- with N
> works too.
> I have tested those statements on S2k Query Analyzer and S2k5 SQL Server
> Management Studio Express and there are same results.
> Have anyone any idea what is wrong?
> Regards,
> Tomasz Rutkowski
>
>
>

Odd connection problem

I (running SQL 7 client only) cannot connect to the
server via my machine, but CAN connect (after installing
client) to the server from ANY other machine on the LAN.
My SysAdmin and I have been workin on this for days but
have come up empty, any ideas?
Could it be some other software I'm running?
Specs:
Sql7 client attempting to connect to server fails via
client tools and isql command line...
client = XP Pro connected via LAN to server. Can ping
server... have re-installed client software multiple
times.
rcause,
What error message do you get when connecting from osql.exe? Do you have an
aliases set up in your client configuration utility?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
|||Mark,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionOpen (Connect()).
That's the osql error message.
Do not have alias set up?
Thanks
>--Original Message--
>rcause,
>What error message do you get when connecting from
osql.exe? Do you have an[vbcol=seagreen]
>aliases set up in your client configuration utility?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rcause" <rcausey@.med.unc.edu> wrote in message
>news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
installing[vbcol=seagreen]
LAN.
>
>.
>
|||Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004
|||Hi ricky
just goto the services in your client mechine.and find out the sql srevre services.
then if they are stop then start.
if they are start then go to the properits.
and go to the logon and check the authentication to local system
biswajit das
-- Ricky Artigas wrote: --
Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

Odd connection problem

I (running SQL 7 client only) cannot connect to the
server via my machine, but CAN connect (after installing
client) to the server from ANY other machine on the LAN.
My SysAdmin and I have been workin on this for days but
have come up empty, any ideas?
Could it be some other software I'm running?
Specs:
Sql7 client attempting to connect to server fails via
client tools and isql command line...
client = XP Pro connected via LAN to server. Can ping
server... have re-installed client software multiple
times.rcause,
What error message do you get when connecting from osql.exe? Do you have an
aliases set up in your client configuration utility?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.|||Mark,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server
does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionOpen (Connect()).
That's the osql error message.
Do not have alias set up?
Thanks
>--Original Message--
>rcause,
>What error message do you get when connecting from
osql.exe? Do you have an
>aliases set up in your client configuration utility?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rcause" <rcausey@.med.unc.edu> wrote in message
>news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
installing[vbcol=seagreen]
LAN.[vbcol=seagreen]
>
>.
>|||Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Could it be some other software I'm running?
> Specs:
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> client = XP Pro connected via LAN to server. Can ping
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004|||Hi ricky
just goto the services in your client mechine.and find out the sql srevre se
rvices.
then if they are stop then start.
if they are start then go to the properits.
and go to the logon and check the authentication to local system
biswajit das
-- Ricky Artigas wrote: --
Use SQL Server Authentication... Open up ODBC Control Panel, set up a DSN
and name it with your server's name. select sql server authentication. HTH.
=)
Ricky Artigas
"rcause" <rcausey@.med.unc.edu> wrote in message
news:fdb901c43ead$883f7160$a101280a@.phx.gbl...
> I (running SQL 7 client only) cannot connect to the
> server via my machine, but CAN connect (after installing
> client) to the server from ANY other machine on the LAN.
> My SysAdmin and I have been workin on this for days but
> have come up empty, any ideas?
> Sql7 client attempting to connect to server fails via
> client tools and isql command line...
> server... have re-installed client software multiple
> times.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004

ODBC using SSH

Hello!
I am trying to use the ODBC services via an SSH connection.
What is the easiest way of doing this? Are there any ready-made software out
there than I can use to connect?
Thank you!
-Bahman
Hi,
ODBC has no clue about SSH. What you need to do is to use an SSH client
that supports tunnelling, like Putty
(http://www.chiark.greenend.org.uk/~sgtatham/putty/)
You will have to read Putty's documentation to see how to setup
tunneling. Once SSH tunelling is setup, you simply put "localhost" as
the remote server name in ODBC DSN. I have this setup on my end. Here is
what I do.
Client on
Internet -> Linux machine via SSH -> MS SQL Server
Regards.
Bahman wrote:
> Hello!
> I am trying to use the ODBC services via an SSH connection.
> What is the easiest way of doing this? Are there any ready-made software out
> there than I can use to connect?
> Thank you!
> -Bahman
>

ODBC using SSH

Hello!
I am trying to use the ODBC services via an SSH connection.
What is the easiest way of doing this? Are there any ready-made software out
there than I can use to connect?
Thank you!
-BahmanHi,
ODBC has no clue about SSH. What you need to do is to use an SSH client
that supports tunnelling, like Putty
(http://www.chiark.greenend.org.uk/~sgtatham/putty/)
You will have to read Putty's documentation to see how to setup
tunneling. Once SSH tunelling is setup, you simply put "localhost" as
the remote server name in ODBC DSN. I have this setup on my end. Here is
what I do.
Client on
Internet -> Linux machine via SSH -> MS SQL Server
Regards.
Bahman wrote:
> Hello!
> I am trying to use the ODBC services via an SSH connection.
> What is the easiest way of doing this? Are there any ready-made software o
ut
> there than I can use to connect?
> Thank you!
> -Bahman
>

ODBC Trusted_Connection call to SQLGetInfo returns DBO as user name, not NT User

Hello, all:
I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo.

So:

Login Type / user id /returned value from SQLGetInfo

Windows Authentication / Windows login / dbo
SQL Authentication / Windows login (UID) / UID

The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server...

My questions:
Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?I think I have finally wrestled this beast to the ground. Here's the story, in case anybody is interested:

1) When making an NTLM trusted connection to MSS, logging in using my NT login name e.g. as amarshall, the system was always saying the connected user is dbo, no matter what. This can be verified by using a SQL tool like SQL Query Analyzer, connecting over a trusted connection to MSS, and entering SELECT USER; or SELECT USER_NAME() or any variants. These will always return dbo, which is not the real 'user' of interest, but the schema owner. What we want is to confirm 'amarshall' is a (legitimate) user, meaning, SQL Server knows who this is..

2) So, use the SYSTEM_USER call, and the result is AMARSHALL-CAM\amarshall (Host\user).

3) Then, just perform substring match to see if what the user has typed into the login dialog (amarshall) matches what is in the call result. If so, good user, otherwise, reject (program decides).

4) I do NOT strip off the host name or anything - just substring match result of SYSTEM_USER call to logins userid.

5) Database connectstring for Powerbuilder must include SECURE=1. For ADO/VB/.NET, has to have something like TRUSTED_CONNECTION=yes (PB does this for me)

6) Have not tested this against the SQL Server Desktop Edition. Should be identical, but who knows?

7) If anybody has a superior resolution, or security concerns, please advise, but this suffices for now.|||dbo is a database user. You seem to want the server login information, which is quite different. I'd use suser_sname (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_86xx.asp).

-PatP

ODBC to SQL Express - SQL Driver versus SQL Native Client

I have a SQL express database which I need to access from a shared hosting plan. I can create an ODBC connection through the hosting provider's control panel for SQL Server, but it won't connect. I tested this locally and discovered that the SQL Native Client connects fine, but the previous SQL Server driver does not. This seems to only happen with SQL 2005 Express edition; it works with the Developer Edition. Does SQL Express only use the Native SQL Client?

Thanks in advance for your help!

NO, it does not, you can connect using the ADO / ADO.NET as well. Which error information do you get ? Are you using a user instance ? This is though only supported by the SNAC client.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you!!! The issue was that it was installed as a user instance and not the default instance. With a user instance, you can apparently only connect using the SQL Native Client. I reinstalled SQL 2005 Express as the default instance and the older SQL Server ODBC driver worked!

odbc to progress db

I have users that thru and ODBC connection to my Sql2000 db attach tables
and query different information. Now they need to read data from a Progress
database on Unix to pull some values from a table. I have the ODBC driver
that allows me to connect to the Progress DB. Could I create a DB in SQL
that is nothing but views to the Progress tables?
I don't want to replicate the data down to Sql.
Any help or suggestions would be much appreciated.
Hi Andy
"Andy" wrote:

> I have users that thru and ODBC connection to my Sql2000 db attach tables
> and query different information. Now they need to read data from a Progress
> database on Unix to pull some values from a table. I have the ODBC driver
> that allows me to connect to the Progress DB. Could I create a DB in SQL
> that is nothing but views to the Progress tables?
> I don't want to replicate the data down to Sql.
>
> Any help or suggestions would be much appreciated.
>
If you have an odbc driver then you may be able to create a linked server
that will allow you users to query the remote database. See
http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
John
|||Do you still create a DB in SQL.
After you create the Link-Server how do the users run queries agisnt the
Progress(Unix) tables?
What does ther syntax look like?
Thanks..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
> Hi Andy
> "Andy" wrote:
> If you have an odbc driver then you may be able to create a linked server
> that will allow you users to query the remote database. See
> http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
> http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
>
> John
|||Hi
You don't need to create a separate database unless you want to keep access
away from your existing databases.
You can access the linked server using four part names such as:
SELECT col1, col2
FROM RemoteServer.RemoteDB..RemoteTable
See http://msdn2.microsoft.com/en-us/library/aa172676(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
You can also use OPENQUERY see
http://msdn2.microsoft.com/en-us/library/aa276848(SQL.80).aspx
If you use stored procedures or views
http://msdn2.microsoft.com/en-us/library/aa258253(SQL.80).aspx to access the
remote database the users do not need to know where the data is.
John
"Andy" wrote:

> Do you still create a DB in SQL.
> After you create the Link-Server how do the users run queries agisnt the
> Progress(Unix) tables?
> What does ther syntax look like?
> Thanks..
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
>
>

odbc to progress db

I have users that thru and ODBC connection to my Sql2000 db attach tables
and query different information. Now they need to read data from a Progress
database on Unix to pull some values from a table. I have the ODBC driver
that allows me to connect to the Progress DB. Could I create a DB in SQL
that is nothing but views to the Progress tables?
I don't want to replicate the data down to Sql.
Any help or suggestions would be much appreciated.Hi Andy
"Andy" wrote:

> I have users that thru and ODBC connection to my Sql2000 db attach tables
> and query different information. Now they need to read data from a Progres
s
> database on Unix to pull some values from a table. I have the ODBC driver
> that allows me to connect to the Progress DB. Could I create a DB in SQL
> that is nothing but views to the Progress tables?
> I don't want to replicate the data down to Sql.
>
> Any help or suggestions would be much appreciated.
>
If you have an odbc driver then you may be able to create a linked server
that will allow you users to query the remote database. See
http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
John|||Do you still create a DB in SQL.
After you create the Link-Server how do the users run queries agisnt the
Progress(Unix) tables?
What does ther syntax look like?
Thanks..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
> Hi Andy
> "Andy" wrote:
>
> If you have an odbc driver then you may be able to create a linked server
> that will allow you users to query the remote database. See
> http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
> http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
>
> John|||Hi
You don't need to create a separate database unless you want to keep access
away from your existing databases.
You can access the linked server using four part names such as:
SELECT col1, col2
FROM RemoteServer.RemoteDB..RemoteTable
See http://msdn2.microsoft.com/en-us/library/aa172676(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
You can also use OPENQUERY see
http://msdn2.microsoft.com/en-us/library/aa276848(SQL.80).aspx
If you use stored procedures or views
http://msdn2.microsoft.com/en-us/library/aa258253(SQL.80).aspx to access the
remote database the users do not need to know where the data is.
John
"Andy" wrote:

> Do you still create a DB in SQL.
> After you create the Link-Server how do the users run queries agisnt the
> Progress(Unix) tables?
> What does ther syntax look like?
> Thanks..
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
>
>

odbc to progress db

I have users that thru and ODBC connection to my Sql2000 db attach tables
and query different information. Now they need to read data from a Progress
database on Unix to pull some values from a table. I have the ODBC driver
that allows me to connect to the Progress DB. Could I create a DB in SQL
that is nothing but views to the Progress tables?
I don't want to replicate the data down to Sql.
Any help or suggestions would be much appreciated.Hi Andy
"Andy" wrote:
> I have users that thru and ODBC connection to my Sql2000 db attach tables
> and query different information. Now they need to read data from a Progress
> database on Unix to pull some values from a table. I have the ODBC driver
> that allows me to connect to the Progress DB. Could I create a DB in SQL
> that is nothing but views to the Progress tables?
> I don't want to replicate the data down to Sql.
>
> Any help or suggestions would be much appreciated.
>
If you have an odbc driver then you may be able to create a linked server
that will allow you users to query the remote database. See
http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
John|||Do you still create a DB in SQL.
After you create the Link-Server how do the users run queries agisnt the
Progress(Unix) tables?
What does ther syntax look like?
Thanks..
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
> Hi Andy
> "Andy" wrote:
>> I have users that thru and ODBC connection to my Sql2000 db attach tables
>> and query different information. Now they need to read data from a
>> Progress
>> database on Unix to pull some values from a table. I have the ODBC driver
>> that allows me to connect to the Progress DB. Could I create a DB in SQL
>> that is nothing but views to the Progress tables?
>> I don't want to replicate the data down to Sql.
>>
>> Any help or suggestions would be much appreciated.
> If you have an odbc driver then you may be able to create a linked server
> that will allow you users to query the remote database. See
> http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
> http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
>
> John|||Hi
You don't need to create a separate database unless you want to keep access
away from your existing databases.
You can access the linked server using four part names such as:
SELECT col1, col2
FROM RemoteServer.RemoteDB..RemoteTable
See http://msdn2.microsoft.com/en-us/library/aa172676(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/ms187879.aspx
You can also use OPENQUERY see
http://msdn2.microsoft.com/en-us/library/aa276848(SQL.80).aspx
If you use stored procedures or views
http://msdn2.microsoft.com/en-us/library/aa258253(SQL.80).aspx to access the
remote database the users do not need to know where the data is.
John
"Andy" wrote:
> Do you still create a DB in SQL.
> After you create the Link-Server how do the users run queries agisnt the
> Progress(Unix) tables?
> What does ther syntax look like?
> Thanks..
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1F399B30-FDAD-41CF-B05D-B56B48FFFBEE@.microsoft.com...
> > Hi Andy
> >
> > "Andy" wrote:
> >
> >> I have users that thru and ODBC connection to my Sql2000 db attach tables
> >> and query different information. Now they need to read data from a
> >> Progress
> >> database on Unix to pull some values from a table. I have the ODBC driver
> >> that allows me to connect to the Progress DB. Could I create a DB in SQL
> >> that is nothing but views to the Progress tables?
> >>
> >> I don't want to replicate the data down to Sql.
> >>
> >>
> >> Any help or suggestions would be much appreciated.
> >>
> > If you have an odbc driver then you may be able to create a linked server
> > that will allow you users to query the remote database. See
> > http://msdn2.microsoft.com/ru-ru/library/aa213778(SQL.80).aspx and
> > http://msdn2.microsoft.com/ru-ru/library/aa259589(SQL.80).aspx for more.
> >
> >
> > John
>
>

odbc timeout

Hi Ralf
I am not sure why you need to create a new ODBC connection if this
previously existed? If this did not previously exist then you can not say fo
r
certain it is the NIC that is causing this, therefore check to see if you ca
n
ping the server and/or connect via osql, check that the firewall is not
blocking any port being used. Other things to look out for would be faulty
cabling, a problem with a hub or switch, out-of-date drivers.
John
"Ralf Temme" wrote:

> Hallo,
> we have to installed a new nic on a w2k-server with sql-server2000. The
> proberties TCP/IP are the same like on the old nic. SQL-Connection are oka
y,
> but not the odbc-connection! During the configuratin of the odbc-connectio
n
> on the client we recieve an timeout error. 1.-3. or 4. try are succesful.
But
> than timeout. Nameresolution seems okay. We have no error massage or id. O
nly
> timeout, connection failed!
> What′s the reason for this? Only a new network-card!
> Thanks for help!Hallo,
we have to installed a new nic on a w2k-server with sql-server2000. The
proberties TCP/IP are the same like on the old nic. SQL-Connection are okay,
but not the odbc-connection! During the configuratin of the odbc-connection
on the client we recieve an timeout error. 1.-3. or 4. try are succesful. Bu
t
than timeout. Nameresolution seems okay. We have no error massage or id. Onl
y
timeout, connection failed!
What′s the reason for this? Only a new network-card!
Thanks for help!|||Hi Ralf
I am not sure why you need to create a new ODBC connection if this
previously existed? If this did not previously exist then you can not say fo
r
certain it is the NIC that is causing this, therefore check to see if you ca
n
ping the server and/or connect via osql, check that the firewall is not
blocking any port being used. Other things to look out for would be faulty
cabling, a problem with a hub or switch, out-of-date drivers.
John
"Ralf Temme" wrote:

> Hallo,
> we have to installed a new nic on a w2k-server with sql-server2000. The
> proberties TCP/IP are the same like on the old nic. SQL-Connection are oka
y,
> but not the odbc-connection! During the configuratin of the odbc-connectio
n
> on the client we recieve an timeout error. 1.-3. or 4. try are succesful.
But
> than timeout. Nameresolution seems okay. We have no error massage or id. O
nly
> timeout, connection failed!
> What′s the reason for this? Only a new network-card!
> Thanks for help!|||Hi John,
we create a new odbc connection only for testing. The error is the same like
the old one. We have applications as sql-clients, works normaly. So I′m sur
e
network connection is okay. One application needs the odbc connection. These
one failed with timeout error. Try to connect passed 3-4 times, than
timeout. After a few seconds I can start again: the same (passed 3-4 times,
than timeout). ODBCPING passed. Ping passed. Firewall is not activ. MDAC is
new. SP4 for SQL-Server is installed. Driver for the NIC (Intel) is the new.
It seems like an limit of odbc-requests, but I don′t know!
"John Bell" schrieb:
[vbcol=seagreen]
> Hi Ralf
> I am not sure why you need to create a new ODBC connection if this
> previously existed? If this did not previously exist then you can not say
for
> certain it is the NIC that is causing this, therefore check to see if you
can
> ping the server and/or connect via osql, check that the firewall is not
> blocking any port being used. Other things to look out for would be faulty
> cabling, a problem with a hub or switch, out-of-date drivers.
> John
> "Ralf Temme" wrote:
>|||Hi Ralf
If there is a limit on your connections then I would expect other systems to
experience problems as well! You could set ODBC tracing on, but I am not sur
e
that it will tell you anything extra. You may also want to try a different
NIC that you know is working. You may also want to try plugging the maching
into the same hub/switch as the server using different cables.
John
"Ralf Temme" wrote:
[vbcol=seagreen]
> Hi John,
> we create a new odbc connection only for testing. The error is the same li
ke
> the old one. We have applications as sql-clients, works normaly. So I′m s
ure
> network connection is okay. One application needs the odbc connection. The
se
> one failed with timeout error. Try to connect passed 3-4 times, than
> timeout. After a few seconds I can start again: the same (passed 3-4 times
,
> than timeout). ODBCPING passed. Ping passed. Firewall is not activ. MDAC i
s
> new. SP4 for SQL-Server is installed. Driver for the NIC (Intel) is the ne
w.
> It seems like an limit of odbc-requests, but I don′t know!
> "John Bell" schrieb:
>|||You say that one app is having problems. What is the MDAC for the app softwa
re?
Is the app using 'generic' odbc or is it writing to the API?
Also, during the set up of the ODBC driver in ODBC Administrator,t here is a
'test connection' option. When you do that test does it fail?
"John Bell" wrote:
[vbcol=seagreen]
> Hi Ralf
> If there is a limit on your connections then I would expect other systems
to
> experience problems as well! You could set ODBC tracing on, but I am not s
ure
> that it will tell you anything extra. You may also want to try a different
> NIC that you know is working. You may also want to try plugging the machin
g
> into the same hub/switch as the server using different cables.
> John
>
> "Ralf Temme" wrote:
>|||Hi John,
we create a new odbc connection only for testing. The error is the same like
the old one. We have applications as sql-clients, works normaly. So I′m sur
e
network connection is okay. One application needs the odbc connection. These
one failed with timeout error. Try to connect passed 3-4 times, than
timeout. After a few seconds I can start again: the same (passed 3-4 times,
than timeout). ODBCPING passed. Ping passed. Firewall is not activ. MDAC is
new. SP4 for SQL-Server is installed. Driver for the NIC (Intel) is the new.
It seems like an limit of odbc-requests, but I don′t know!
"John Bell" schrieb:
[vbcol=seagreen]
> Hi Ralf
> I am not sure why you need to create a new ODBC connection if this
> previously existed? If this did not previously exist then you can not say
for
> certain it is the NIC that is causing this, therefore check to see if you
can
> ping the server and/or connect via osql, check that the firewall is not
> blocking any port being used. Other things to look out for would be faulty
> cabling, a problem with a hub or switch, out-of-date drivers.
> John
> "Ralf Temme" wrote:
>|||Hi Ralf
If there is a limit on your connections then I would expect other systems to
experience problems as well! You could set ODBC tracing on, but I am not sur
e
that it will tell you anything extra. You may also want to try a different
NIC that you know is working. You may also want to try plugging the maching
into the same hub/switch as the server using different cables.
John
"Ralf Temme" wrote:
[vbcol=seagreen]
> Hi John,
> we create a new odbc connection only for testing. The error is the same li
ke
> the old one. We have applications as sql-clients, works normaly. So I′m s
ure
> network connection is okay. One application needs the odbc connection. The
se
> one failed with timeout error. Try to connect passed 3-4 times, than
> timeout. After a few seconds I can start again: the same (passed 3-4 times
,
> than timeout). ODBCPING passed. Ping passed. Firewall is not activ. MDAC i
s
> new. SP4 for SQL-Server is installed. Driver for the NIC (Intel) is the ne
w.
> It seems like an limit of odbc-requests, but I don′t know!
> "John Bell" schrieb:
>|||You say that one app is having problems. What is the MDAC for the app softwa
re?
Is the app using 'generic' odbc or is it writing to the API?
Also, during the set up of the ODBC driver in ODBC Administrator,t here is a
'test connection' option. When you do that test does it fail?
"John Bell" wrote:
[vbcol=seagreen]
> Hi Ralf
> If there is a limit on your connections then I would expect other systems
to
> experience problems as well! You could set ODBC tracing on, but I am not s
ure
> that it will tell you anything extra. You may also want to try a different
> NIC that you know is working. You may also want to try plugging the machin
g
> into the same hub/switch as the server using different cables.
> John
>
> "Ralf Temme" wrote:
>