No longer able to connect to server

SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
changed the password of the login to Windows and now I can no longer connect
to SQL Server to get it to start.
Help,
Thanks.
Doug,
Now change the password in Control Panel - Services for the SQL Server and
SQL Server Agent services. Then restart them there. Password changes at a
domain level do not "trickle down".
HTH
Jerry
"Doug Broadwell" <Doug Broadwell@.discussions.microsoft.com> wrote in message
news:615A00DE-AA31-4507-A031-E982F312F612@.microsoft.com...
> SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
> changed the password of the login to Windows and now I can no longer
> connect
> to SQL Server to get it to start.
> Help,
> Thanks.
|||That did the trick, thanks so much!
Doug
"Jerry Spivey" wrote:

> Doug,
> Now change the password in Control Panel - Services for the SQL Server and
> SQL Server Agent services. Then restart them there. Password changes at a
> domain level do not "trickle down".
> HTH
> Jerry
> "Doug Broadwell" <Doug Broadwell@.discussions.microsoft.com> wrote in message
> news:615A00DE-AA31-4507-A031-E982F312F612@.microsoft.com...
>
>

No longer able to connect to server

SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
changed the password of the login to Windows and now I can no longer connect
to SQL Server to get it to start.
Help,
Thanks.Doug,
Now change the password in Control Panel - Services for the SQL Server and
SQL Server Agent services. Then restart them there. Password changes at a
domain level do not "trickle down".
HTH
Jerry
"Doug Broadwell" <Doug Broadwell@.discussions.microsoft.com> wrote in message
news:615A00DE-AA31-4507-A031-E982F312F612@.microsoft.com...
> SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
> changed the password of the login to Windows and now I can no longer
> connect
> to SQL Server to get it to start.
> Help,
> Thanks.|||That did the trick, thanks so much!
Doug
"Jerry Spivey" wrote:

> Doug,
> Now change the password in Control Panel - Services for the SQL Server and
> SQL Server Agent services. Then restart them there. Password changes at
a
> domain level do not "trickle down".
> HTH
> Jerry
> "Doug Broadwell" <Doug Broadwell@.discussions.microsoft.com> wrote in messa
ge
> news:615A00DE-AA31-4507-A031-E982F312F612@.microsoft.com...
>
>

No longer able to connect to server

SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
changed the password of the login to Windows and now I can no longer connect
to SQL Server to get it to start.
Help,
Thanks.Doug,
Now change the password in Control Panel - Services for the SQL Server and
SQL Server Agent services. Then restart them there. Password changes at a
domain level do not "trickle down".
HTH
Jerry
"Doug Broadwell" <Doug Broadwell@.discussions.microsoft.com> wrote in message
news:615A00DE-AA31-4507-A031-E982F312F612@.microsoft.com...
> SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
> changed the password of the login to Windows and now I can no longer
> connect
> to SQL Server to get it to start.
> Help,
> Thanks.|||That did the trick, thanks so much!
Doug
"Jerry Spivey" wrote:
> Doug,
> Now change the password in Control Panel - Services for the SQL Server and
> SQL Server Agent services. Then restart them there. Password changes at a
> domain level do not "trickle down".
> HTH
> Jerry
> "Doug Broadwell" <Doug Broadwell@.discussions.microsoft.com> wrote in message
> news:615A00DE-AA31-4507-A031-E982F312F612@.microsoft.com...
> > SQL Server 2000 using Windows Authication (Win2K) to log onto server. I
> > changed the password of the login to Windows and now I can no longer
> > connect
> > to SQL Server to get it to start.
> >
> > Help,
> > Thanks.
>
>

no external connections are permitted while trying to connect to SQL server

ok so this is my code:

private void iDListBox_SelectedIndexChanged(object sender, EventArgs e)

{

string connectionString = GetConnectionString();

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("SELECT * FROM Table1 WHERE Naam=@.Naam", connection);

cmd.Parameters.AddWithValue("@.Naam", iDListBox.SelectedValue);

connection.Open();

SqlDataReader reader = cmd.ExecuteReader();

if (reader.Read())

{

naamTextBox.Text = reader.GetString(1);

}

reader.Dispose();

connection.Dispose();

}

}

static private string GetConnectionString()

{

return "Data Source=(local);Initial Catalog=AdventureWorks;"

+ "Integrated Security=SSPI;";

}

but when I change the selected index of the listbox, then this error comes up:

System.Data.SqlClient.SqlException was unhandled
Message="An error occured while setting up a connection with the server. At making a connection with SQL server 2005, this jamming is possibly caused by the fact that at the standardinstitutions of SQL server no external connections have been permitted (provider: Provider of named pipes, error: 40 - no connection with SQL could open server)"

If the error sounds a bit weird, sry I tried to translate as most of my errors come in my language

someone knows what the problem is and a solution to it?

(btw sry if this is in the wrong part of the sql forum, but there are so much parts, I didn't know which to choose lol )

thx in advance

Please see http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx to see if any of the suggestions solves your case.

Jimmy

No Excel 2007 Table Support in SSIS?

When I connect to an Excel 2007 workbook using the new Microsoft.ACE.Oledb.12.0 provider in SSIS 2005, I notice that any tables that I've created in the worksheet are not recognized in SSIS.

In the OLE DB data source component (using the "Table or View" data access mode) the any table(s) I've created are nowhere to be found in the drop down list. Similarly, when I constuct a simple SQL query on the workbook, such as "SELECT * From MyTable" it returns the error:

Microsoft Office Access Database Engine

Hresult: 0x80004005

Description: "The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly."

I know I have the name right -- I can use structured references to my named table in the worksheet without any problems.

Does the new ACE provider not support Excel 2007 tables? Am I stuck with using "overlapping" cell references to capture data from tables in my worksheet?

I see that you are using Named Ranges. What if you use the worksheet name?

The Office 12 versions of the Jet Provider and Excel Driver are rewritten, so it's possible that behavior has changed, whether intentionally or unintentionally. Previously the driver displayed both sheets and named ranges as "tables".

-Doug

|||

Apologies for not making myself clearer. I'm referring to the improved table objects in Excel 2007, discussed here:

http://blogs.msdn.com/excel/archive/2005/10/25/484915.aspx

When extracting data from an Excel 2007 source, I'm able to access all of the information on the sheet or from my named ranges without any problem -- both appear as what SSIS called "tables" in the OLE DB Source dialog box. This behavior is the exact same as it was using the JET provider.

My problem is that the Excel 2007 table objects that I've created in various sheets simply aren't accessible -- they don't show up at all. Given their structured, unifiorm nature, one would expect an Excel table to be recognized by the new ACE provider just like, say, a named range.

Why named tables instead of simple named ranges?

-- Tables can grow dynamically as you add rows, and named ranges cannot. My thinking was that if you could import an Excel 2007 table, you wouldn't have to worry if the named range was large enough.

-- Excel 2007 tables can be filtered and sorted, giving the user in the workbook a richer experience.

I think it would be very powerful and useful to access data as it is represented in an Excel 2007 table object through SSIS. I haven't been able to determine if this is even possible, or if I need to take some special steps to make it work.

|||

The driver has no knowledge of any new features in Excel 2007. This is merely the legacy driver, updated to connect successfully to Excel 2007 files, which as you know use a different file format.

-Doug

|||

Thanks for your quick reply -- looks like I'll have to go back to named references.

Also, for anyone wanting to do a similar thing with "Lists" in Excel 2003 (the precursor to Excel 2007) a quick test seems to indicate that the Excel driver (for pre-2007 versions) does not support them, either.

No connection to local SQL Server when no network connection

The soluition is to use named pipes to connect as
Enterprise manager will if IP: 127.0.0.1 cannot connect.
Change your connection string to :
'Provider=SQLOLEDB.1; Password="";Persist Security
Info=True;User
ID=Bis;Initial Catalog=BIS;Data Source=(local)'
This should fix the problem.

>--Original Message--
>Hi !
>We are using Windows XP,
>ADO in our application,
>MS SQL Server 2000.
>(Programming language: Delphi 5;
> ADO components: from Adonis;
> The computer is a SONY notebook).
>SQL server is running on the same machine as the
application.
>The application has worked in many, many cases under W2K
and even XP.
>But on that computer it doesn't work:
>When there is no network connection, then the application
CANNOT connect to
>our DB.
>On the other hand, Enterprise Manager CAN connect.
>When we plug in the network and restart the computer,
then the application
>CAN connect.
>However, SQL Server is running locally !
>The connection string for connecting our app to the
database is something
>like:
> 'Provider=SQLOLEDB.1; Password="";Persist Security
Info=True;User
>ID=Bis;Initial Catalog=BIS;Data Source=127.0.0.1'
>How can we fix that problem ?
>Is this a problem of MS SQL Server administration ?
>Your kind help would be appreciated.
>Peter
>
>.
>
> Change your connection string to :
> 'Provider=SQLOLEDB.1; Password="";Persist Security
> Info=True;User
> ID=Bis;Initial Catalog=BIS;Data Source=(local)'
It worked.
Thanks a lot !

No connection

Hello,

New development machine with Sql Server Express and Visual Web Developer.

I created a database then tried to connect from a test page on localhost and it worked. Then I tried from the internet domain and could not. So under the

Surface Area Configuration, I change the Remote Connections to Using both TCP/IP and named pipes and restarted the Database Engine. But

I still could not connect. So I restarted the machine. Now I can't view the page on localhost either. I can view a different web page so the Web server is

working.

I get the following as the exception:Error reading the database: Cannot open user default database. Login failed.Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

My connection string is: "Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDBFilename=|DataDirectory|Externalresearch.mdf;User Instance=True"

Any suggestions?

Okay I found out why. If I have Web Developer open (with the database connection open) I cannot connect from the browser. But

if I close Web Developer then I can connect from the browser (ie, view my test page).

Is this because it's the same machine that I am both viewing the web page and hosting Sql server (ie, the user can only be used once)?

|||

Hi,

It's a little strange that it says you're using the Network Service to connect to SQL Server.

Are you connecting to the SQL Server through Internet with the windows authentication? If so, that's not supported, since the windows identity cannot be passed through internet.

|||

Kevin,

Everything including the database is on one machine. I'm using Windows authentication. So when I'm in Visual developer and connect

to the database, my web page (again hosted on the same machine) cannot connect to the database. It's a new installation of everything

with defaults setup. How can I work on the database while my web pages can still access the database? I just downloaded Managament

Studio Express, will this do?

Thanks for any help

|||

Hi,

Yes, you can use Management Studio to grant database access permission to the Network Service account.

By default, it does not have permission to the SQL Server. After granting the access, it will be working fine, I think.

sql

No access from Application to SQL Server 2005 - Solved

Hi

I have installed the 2005 SQL Server and its up running. I can connect to the database direct as administrator, but my simple test application returns an exception.

SqlConnection con = new SqlConnection("server=mercur\\sqlserver2005,database=AdventureWorks;integrated security=true");

con.Open();

As the output confirms it might be the default settings. Im not familiar with the enviroment and will appreciate your help.

Mosquito

I receive this exception:

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at HelloWorld.Form1.button1_Click(Object sender, EventArgs e) in D:\Visual Studio 2005\Projects\HelloWorld\HelloWorld\Form1.cs:line 26

The reason is given in the Exception. The connection string is not valid.

The separation between server string and database string has to be a semicolon and everything works just fine.

No access from Application to SQL Server 2005

Hi

I have installed the 2005 SQL Server and its up running. I can connect to the database direct as administrator, but my simple test application returns an exception.

SqlConnection con = new SqlConnection("server=mercur\\sqlserver2005,database=AdventureWorks;integrated security=true");

con.Open();

As the output confirms it might be the default settings. Im not familiar with the enviroment and will appreciate your help.

Mosquito

I receive this exception:

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at HelloWorld.Form1.button1_Click(Object sender, EventArgs e) in D:\Visual Studio 2005\Projects\HelloWorld\HelloWorld\Form1.cs:line 26

The reason is given in the Exception. The connection string is not valid.

The separation between server string and database string has to be a semicolon and everything works just fine.

No access from Application to SQL Server 2000

I have installed the SQL Server 2000 and its up running.

I can connect to the database direct as administrator, but my test application(asp.net 2.0) returns an exception.

SqlConnection sqlConn = new SqlConnection("data source=10.61.90.24;initial catalog=testdb;persist security info=False;user id=sa;pwd=data;Type System Version=SQL Server 2000;");

sqlConn.Open();

I receive this exception:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This is a configuration problem. You need to go into the 'SQL Server Configuration' tool and enable Named Pipes.

Regards

Paul.

|||

I have enabled Named Pipes.The connection is still failed.

thanks.

Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?

For reference: My current XMLA query to discover the datasources is:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code

Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");

and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).

Anyway, thanks for your help!
Philipp

Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?

For reference: My current XMLA query to discover the datasources is:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code

Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");

and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).

Anyway, thanks for your help!
Philipp

Newbie: Use XMLA to connect to SQL Server

Hi,

I am developing a Java application which is able to browse "XMLA-Type" databases. For testing, we have a SQL Server 2000 and ~2005 installed on a server an I can access them through a web service.

When I query the datasources via XMLA, I get a response like:

DataSourceInfo == Local Analysis Server
ProviderName == Microsoft XML for Analysis
ProviderType == TDP
ProviderType == MDP
ProviderType == DMP
AuthenticationMode == Unauthenticated

(in XML of course, but for simplicity, I leave the XML out...).
Now, this is all well and good, but I do have two -- probably very foolish -- questions:

1. How can I set the SQL Server 2000 to actually use an "authenticated" authentication mode, and
2. (more importantly) How do I actually provide the username/password combination in my XMLA query? Do I set properties? Or restrictions?

For reference: My current XMLA query to discover the datasources is:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

So, suppose I'll get an "AuthenticationMode==Authenticated" in the response, how would I rephrase my discoverAnything queries? (Which currently look like this -- for catalogs):

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlnsOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urnchemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>Local Analysis Server</DataSourceInfo>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Thanks for any help!
Regards,
Philipp
Is my question just hilarilously stupid because I did not understand a concept? In that case, if you could provide me with pointers to where I can find information on authentication with XMLA (I tried google of course, but I did not find much), I'd be thankful.

Regards,
Philipp
|||

Hi Phillip, your question is not stupid, your problem is that there are not many people that actually connect using raw XMLA. There are a lot of moving parts that you need to get right.

Chris Harrington has the best collection of blog posts I have seen on the subject here

http://www.activeinterface.com/thinolap.html

Particularly this one: http://www.activeinterface.com/b2005_11_21.html

Most of his samples are in javascript, but you should be able to figure it out. I know that I was able to setup XMLA and get Chris's samples working on my old laptop, but I do not have it configured on my current one.

How were you planning to authenticate? Were you going to use windows authentication, pass a username/password or are you happy with anonymous authentication?

|||Hi Darren,

thanks for your response -- now I know that at least I did not completely make a fool out of myself. I will check out the blog immediately and try to get it to work.

As for the authentication method: The thing is that at our company, we have set up a web-service with a MS Analysis Server running on it. _I_ can connect to it anonymously (or so it seems: I can just send a "discover catalogs" xmla call and get all that I wanted).
I am of course not at all happy with that. That's why I posted my first question in the post: How do I tell MS Server to use user/password combination (simple authentication)?

So, I'd be most happy if I could support all authentication modes, but for starters, passing a username/password would be just fine.
Again: Thanks for the links. They should get me started,
|||

The key to this in Chris's samples appears to be the following line (which I have simplified a bit) which opens the connection.

oHttp.open("POST", sUrl, false, sDomain + "\\" + sUser, sPass)

So the username/password is specified when opening the connection, before sending any commands. If you have not sent through a username/password, it will be falling back to either anonymous or integrated authentication.|||Thanks again, Darren. Indeed, I found the same line and implemented the same solution in java, that is, I have the code

Authenticator.setDefault(new Authenticator(){
protected PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(username,
password.toCharArray());
}
});
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");

and this does the trick. Or rather: This _seems to_ do the trick: I am still struggling to get my Microsoft Server to use authentication... It seems as if anyone who has access rights to the computer on which the service runs, can also access the database. But, I am pretty sure, I can get that to work (there's a manual to go through).

Anyway, thanks for your help!
Philipp

Newbie: MSDE no longer listens to outside request. Event id 19011.

Hi
I've had a MSDE 2000 instance running a couple of months without any
problems. Now all of a sudden I can only connect to it from localhost.
When I restart the sql-server using the Service Manager it gives a
warning in the event log with event-ID 19011. Error code 1355.
Also I have a bunch of informational events with event-id 3 and
something about some missing file. I'm not sure if the two event-types
have something to do with each other, though.
I believe my problems started after installing a very simple program
called Dyndns Updater. Looking in the install-log I found that it only
installs the main exe, some txt-files and a normal run-parameter in the
registry. To me it seems unlikely that it should interfere with MSDE in
any way. Uninstalling doesn't help either.
All other services on my server are working normally from another computer.
I'm kind of stuck here, not wanting to uninstall/reinstall MSDE.
Any pointers?
Regards
dne Hovda
GTR AS
NORWAY
Michael C# wrote:
> How about firewalls? Are you running MSDE on Win XP and have you installed
> SP 2 recently? The Windows Firewall will, by default, prevent responses to
> inbound traffic from Ports 1433 (TCP) and 1434 (UDP). If this is your
> problem, try opening up these two ports on your Windows Firewall.
Sorry, I forgot to specify.
We have a pretty simple network of 6 machines, no internal firewalls.
MSDE runs on W2KSP4. I've tried both with and without firewall on the
client-machines that all run XPSP2. It doesn't seem to be a
firewall-related problem. Also, I checked that IP-filtering is turned
off on the server.
Looking deeper into the logs, I found that the program I before
mentioned has definetely nothing to do with it, as I see the same error
(19011) at least two more times, exactly one and two weeks ago, to be
specific.
dne
|||Chris V. wrote:
> What is the size of the MDF file, or the LDF ?
> If I remember properly, the max size is 2Gb, and if I am still right, the
> 2Gb apply to the physical file, to the size of the active pages.
The total size of all databases in the instance is 58MB...
This is very strange: All of a sudden it just started working again! I
can now connect from all clients. I haven't changed anything.
Hm. This problem might not be MSDE related after all. Still I'm
wondering what those error messages mean, though.
Anyway, thanks for your help, guys.
dne

Newbie: MSDE no longer listens to outside request. Event id 19011.

Hi
I've had a MSDE 2000 instance running a couple of months without any
problems. Now all of a sudden I can only connect to it from localhost.
When I restart the sql-server using the Service Manager it gives a
warning in the event log with event-ID 19011. Error code 1355.
Also I have a bunch of informational events with event-id 3 and
something about some missing file. I'm not sure if the two event-types
have something to do with each other, though.
I believe my problems started after installing a very simple program
called Dyndns Updater. Looking in the install-log I found that it only
installs the main exe, some txt-files and a normal run-parameter in the
registry. To me it seems unlikely that it should interfere with MSDE in
any way. Uninstalling doesn't help either.
All other services on my server are working normally from another computer.
I'm kind of stuck here, not wanting to uninstall/reinstall MSDE.
Any pointers?
Regards
dne Hovda
GTR AS
NORWAYMichael C# wrote:
> How about firewalls? Are you running MSDE on Win XP and have you installe
d
> SP 2 recently? The Windows Firewall will, by default, prevent responses t
o
> inbound traffic from Ports 1433 (TCP) and 1434 (UDP). If this is your
> problem, try opening up these two ports on your Windows Firewall.
Sorry, I forgot to specify.
We have a pretty simple network of 6 machines, no internal firewalls.
MSDE runs on W2KSP4. I've tried both with and without firewall on the
client-machines that all run XPSP2. It doesn't seem to be a
firewall-related problem. Also, I checked that IP-filtering is turned
off on the server.
Looking deeper into the logs, I found that the program I before
mentioned has definetely nothing to do with it, as I see the same error
(19011) at least two more times, exactly one and two weeks ago, to be
specific.
dne|||Chris V. wrote:
> What is the size of the MDF file, or the LDF ?
> If I remember properly, the max size is 2Gb, and if I am still right, the
> 2Gb apply to the physical file, to the size of the active pages.
The total size of all databases in the instance is 58MB...
This is very strange: All of a sudden it just started working again! I
can now connect from all clients. I haven't changed anything.
Hm. This problem might not be MSDE related after all. Still I'm
wondering what those error messages mean, though.
Anyway, thanks for your help, guys.
dne

Newbie: MSDE no longer listens to outside request. Event id 19011.

Hi
I've had a MSDE 2000 instance running a couple of months without any
problems. Now all of a sudden I can only connect to it from localhost.
When I restart the sql-server using the Service Manager it gives a
warning in the event log with event-ID 19011. Error code 1355.
Also I have a bunch of informational events with event-id 3 and
something about some missing file. I'm not sure if the two event-types
have something to do with each other, though.
I believe my problems started after installing a very simple program
called Dyndns Updater. Looking in the install-log I found that it only
installs the main exe, some txt-files and a normal run-parameter in the
registry. To me it seems unlikely that it should interfere with MSDE in
any way. Uninstalling doesn't help either.
All other services on my server are working normally from another computer.
I'm kind of stuck here, not wanting to uninstall/reinstall MSDE.
Any pointers?
Regards
Ådne Hovda
GTR AS
NORWAYHow about firewalls? Are you running MSDE on Win XP and have you installed
SP 2 recently? The Windows Firewall will, by default, prevent responses to
inbound traffic from Ports 1433 (TCP) and 1434 (UDP). If this is your
problem, try opening up these two ports on your Windows Firewall.
Thx
"Ådne Hovda" <aadne@.hovda.no> wrote in message
news:eK4kSdRFFHA.392@.TK2MSFTNGP14.phx.gbl...
> Hi
> I've had a MSDE 2000 instance running a couple of months without any
> problems. Now all of a sudden I can only connect to it from localhost.
> When I restart the sql-server using the Service Manager it gives a warning
> in the event log with event-ID 19011. Error code 1355.
> Also I have a bunch of informational events with event-id 3 and something
> about some missing file. I'm not sure if the two event-types have
> something to do with each other, though.
> I believe my problems started after installing a very simple program
> called Dyndns Updater. Looking in the install-log I found that it only
> installs the main exe, some txt-files and a normal run-parameter in the
> registry. To me it seems unlikely that it should interfere with MSDE in
> any way. Uninstalling doesn't help either.
> All other services on my server are working normally from another
> computer.
> I'm kind of stuck here, not wanting to uninstall/reinstall MSDE.
> Any pointers?
> Regards
> Ådne Hovda
> GTR AS
> NORWAY|||Michael C# wrote:
> How about firewalls? Are you running MSDE on Win XP and have you installed
> SP 2 recently? The Windows Firewall will, by default, prevent responses to
> inbound traffic from Ports 1433 (TCP) and 1434 (UDP). If this is your
> problem, try opening up these two ports on your Windows Firewall.
Sorry, I forgot to specify.
We have a pretty simple network of 6 machines, no internal firewalls.
MSDE runs on W2KSP4. I've tried both with and without firewall on the
client-machines that all run XPSP2. It doesn't seem to be a
firewall-related problem. Also, I checked that IP-filtering is turned
off on the server.
Looking deeper into the logs, I found that the program I before
mentioned has definetely nothing to do with it, as I see the same error
(19011) at least two more times, exactly one and two weeks ago, to be
specific.
Ådne|||What is the size of the MDF file, or the LDF ?
If I remember properly, the max size is 2Gb, and if I am still right, the
2Gb apply to the physical file, to the size of the active pages.
Chris
"Ådne Hovda" <aadne@.hovda.no> a écrit dans le message de news:
eFSW7zRFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Michael C# wrote:
>> How about firewalls? Are you running MSDE on Win XP and have you
>> installed SP 2 recently? The Windows Firewall will, by default, prevent
>> responses to inbound traffic from Ports 1433 (TCP) and 1434 (UDP). If
>> this is your problem, try opening up these two ports on your Windows
>> Firewall.
> Sorry, I forgot to specify.
> We have a pretty simple network of 6 machines, no internal firewalls.
> MSDE runs on W2KSP4. I've tried both with and without firewall on the
> client-machines that all run XPSP2. It doesn't seem to be a
> firewall-related problem. Also, I checked that IP-filtering is turned off
> on the server.
> Looking deeper into the logs, I found that the program I before mentioned
> has definetely nothing to do with it, as I see the same error (19011) at
> least two more times, exactly one and two weeks ago, to be specific.
> Ådne|||Chris V. wrote:
> What is the size of the MDF file, or the LDF ?
> If I remember properly, the max size is 2Gb, and if I am still right, the
> 2Gb apply to the physical file, to the size of the active pages.
The total size of all databases in the instance is 58MB...
This is very strange: All of a sudden it just started working again! I
can now connect from all clients. I haven't changed anything.
Hm. This problem might not be MSDE related after all. Still I'm
wondering what those error messages mean, though.
Anyway, thanks for your help, guys.
Ådne