ASP to SQL 2005 - won't connect

Hi All,
I'm having a bit of a nightmare and could really do with some help.
Our office server is running SQL 2005 Express.
We have installed SQL 2005 Express in our remote web server. I want the web
server to 'collect' data from the office server over night. We cannot get
the two to connect.
We have an ASP file on our web server with following code:
<%
Dim connL
Set connL = Server.CreateObject("ADODB.Connection")
connL.open "Provider=SQLOLEDB.1;Password=password;Persist Security
Info=True;User ID=user;Initial Catalog=OfficeCraft;Data Source=ip
address;Network Library=DBMSSOCN"
%>
When we run this page we get the error message:
[DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection
Please help.
Regards
Andrew Ellinas
By default, SQL-Server 2005 Express won't allow for remote connection so you
have to enable TCP/IP using the the SQL Server Surface Area Configuration
tool. You should also make sure that there is no firewall blocking the port
1433. For reason I don't remember, it's better to simply use SQLOLEDB
instead of SQLOLEDB.1 and in the case of SQL-Server 2005, it's even better
to use the new provider SQLNCLI; see
http://connectionstrings.com/?carrier=sqlserver2005 . Finally, if you have
installed a named instance instead of the default instance, you must add the
name of the instance after the address of the server.
Are you able to connect to it using either MSSMS or MSSMSE?
Here a some other articles to help you troubleshooting your connection
problem:
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
http://msdn2.microsoft.com/en-us/library/ms345318.aspx
http://www.datamasker.com/SSE2005_NetworkCfg.htm (second site for
connection strings for sql providers)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Andrew Ellinas" <Andrew Ellinas@.discussions.microsoft.com> wrote in message
news:889ABEEF-F32B-4C1B-9B1A-C813367C8BF4@.microsoft.com...
> Hi All,
> I'm having a bit of a nightmare and could really do with some help.
> Our office server is running SQL 2005 Express.
> We have installed SQL 2005 Express in our remote web server. I want the
> web
> server to 'collect' data from the office server over night. We cannot get
> the two to connect.
> We have an ASP file on our web server with following code:
> <%
> Dim connL
> Set connL = Server.CreateObject("ADODB.Connection")
> connL.open "Provider=SQLOLEDB.1;Password=password;Persist Security
> Info=True;User ID=user;Initial Catalog=OfficeCraft;Data Source=ip
> address;Network Library=DBMSSOCN"
> %>
> When we run this page we get the error message:
> [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection
> Please help.
> Regards
> Andrew Ellinas
>
>
|||Thank you for taking time to post such a full answer.
I will work my way through what you suggest and post what happens.
Once again, thank you.
"Andrew Ellinas" wrote:

> Hi All,
> I'm having a bit of a nightmare and could really do with some help.
> Our office server is running SQL 2005 Express.
> We have installed SQL 2005 Express in our remote web server. I want the web
> server to 'collect' data from the office server over night. We cannot get
> the two to connect.
> We have an ASP file on our web server with following code:
> <%
> Dim connL
> Set connL = Server.CreateObject("ADODB.Connection")
> connL.open "Provider=SQLOLEDB.1;Password=password;Persist Security
> Info=True;User ID=user;Initial Catalog=OfficeCraft;Data Source=ip
> address;Network Library=DBMSSOCN"
> %>
> When we run this page we get the error message:
> [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection
> Please help.
> Regards
> Andrew Ellinas
>
>

ASP to SQL 2005 - won't connect

Hi All,
I'm having a bit of a nightmare and could really do with some help.
Our office server is running SQL 2005 Express.
We have installed SQL 2005 Express in our remote web server. I want the web
server to 'collect' data from the office server over night. We cannot get
the two to connect.
We have an ASP file on our web server with following code:
<%
Dim connL
Set connL = Server.CreateObject("ADODB.Connection")
connL.open "Provider=SQLOLEDB.1;Password=password;Persist Security
Info=True;User ID=user;Initial Catalog=OfficeCraft;Data Source=ip
address;Network Library=DBMSSOCN"
%>
When we run this page we get the error message:
[DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connection
Please help.
Regards
Andrew EllinasBy default, SQL-Server 2005 Express won't allow for remote connection so you
have to enable TCP/IP using the the SQL Server Surface Area Configuration
tool. You should also make sure that there is no firewall blocking the port
1433. For reason I don't remember, it's better to simply use SQLOLEDB
instead of SQLOLEDB.1 and in the case of SQL-Server 2005, it's even better
to use the new provider SQLNCLI; see
http://connectionstrings.com/?carrier=sqlserver2005 . Finally, if you have
installed a named instance instead of the default instance, you must add the
name of the instance after the address of the server.
Are you able to connect to it using either MSSMS or MSSMSE?
Here a some other articles to help you troubleshooting your connection
problem:
http://blogs.msdn.com/sqlexpress/ar.../05/415084.aspx
http://blogs.msdn.com/sqlexpress/ar.../23/192044.aspx
http://msdn2.microsoft.com/en-us/library/ms345318.aspx
http://www.datamasker.com/SSE2005_NetworkCfg.htm (second site for
connection strings for sql providers)
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Andrew Ellinas" <Andrew Ellinas@.discussions.microsoft.com> wrote in message
news:889ABEEF-F32B-4C1B-9B1A-C813367C8BF4@.microsoft.com...
> Hi All,
> I'm having a bit of a nightmare and could really do with some help.
> Our office server is running SQL 2005 Express.
> We have installed SQL 2005 Express in our remote web server. I want the
> web
> server to 'collect' data from the office server over night. We cannot get
> the two to connect.
> We have an ASP file on our web server with following code:
> <%
> Dim connL
> Set connL = Server.CreateObject("ADODB.Connection")
> connL.open "Provider=SQLOLEDB.1;Password=password;Persist Security
> Info=True;User ID=user;Initial Catalog=OfficeCraft;Data Source=ip
> address;Network Library=DBMSSOCN"
> %>
> When we run this page we get the error message:
> [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connecti
on
> Please help.
> Regards
> Andrew Ellinas
>
>|||Thank you for taking time to post such a full answer.
I will work my way through what you suggest and post what happens.
Once again, thank you.
"Andrew Ellinas" wrote:

> Hi All,
> I'm having a bit of a nightmare and could really do with some help.
> Our office server is running SQL 2005 Express.
> We have installed SQL 2005 Express in our remote web server. I want the w
eb
> server to 'collect' data from the office server over night. We cannot get
> the two to connect.
> We have an ASP file on our web server with following code:
> <%
> Dim connL
> Set connL = Server.CreateObject("ADODB.Connection")
> connL.open "Provider=SQLOLEDB.1;Password=password;Persist Security
> Info=True;User ID=user;Initial Catalog=OfficeCraft;Data Source=ip
> address;Network Library=DBMSSOCN"
> %>
> When we run this page we get the error message:
> [DBNETLIB][ConnectionOpen (ParseConnectParams()).]Invalid connecti
on
> Please help.
> Regards
> Andrew Ellinas
>
>sql

ASP and SQLServer 2k Issue

All,

I have scoured all over and I think I have a problem that is a bit more complex. I have a basic 3tier app running on Win2k, SQLServer 2k and asp.net. I set the aspnet account up with access to the db. I have the following code in my form:

<code>

Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs) Dim MyConn as SQLConnection= NEw SQLConnection("server=xxx,1433;database=bsld_demo;uid=bsld;password=bsld") Dim MySQL As SQLCommand, TID as string, TParam as SQLParameter MySQL= New SqlCommand("InsertRecord", MyConn) MySQL.CommandType = CommandType.StoredProcedure MySQL.Parameters.Add(New SQLParameter("@.compmodule", compmodule.text)) MySQL.Parameters.Add(New SQLParameter("@.customername", customername.text)) TParam=MySQL.Parameters.Add(New SQLParameter("@.ID", 0)) TParam.Direction=ParameterDirection.Output MyConn.Open() MySQL.ExecuteNonQuery TID=TParam.Value MyConn.Close() End Sub

</code>

When I click on the button I get:

Login failed for user 'bsld'.

I am not sure what to do. The odd thing is on my server I have two instances of sqlserver running. Instance 1 is where I am having the problem. Instance works fine. Anybody run into this before? Thanks

Cordell

It is actually very simple the first thing you do is to register one with the other, by right clicking at the top of Enterprise Manager and registering the instance 1 with the instance. Now to your error it means Asp.net does not have an acount in the instance you are trying to connect to. There are two permissions in SQL Server, the server permission under security in the Management section of Enterprise Manager and the database permission by right click on your database go to all tasks. Then you adjust your Web.Config as needed. In the future it is better to backup and restore so everything including permission is moved so you just adjust the Web.Config. Hope this helps.|||

Sorry for the delay. Here is what I have:

<config file >

<configuration>

<appSettings/><connectionStrings>

<addname="bsld_demoConnectionString"

connectionString="Data Source=server\instance1;Initial Catalog=bsld_demo;Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

<system.web>

<customErrorsmode="Off"/>

<

authenticationmode="Windows"/>

<!--

</system.web>

</

configuration>

<config/>

<script>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Dim MyConnAs SqlConnection =New SqlConnection("server=server,1433;Integrated Security=SSPI;database=bsld_demo")Dim MySQLAs SQLCommand, RIDAsString, TParamAs SQLParameter

MySQL =

New SqlCommand("InsertRecord", MyConn)

MySQL.CommandType = CommandType.StoredProcedure

MySQL.Parameters.Add(

New SqlParameter("@.CompModule", CompModule.text))

MySQL.Parameters.Add(

New SqlParameter("@.CustomerName", CustomerName.text))

TParam = MySQL.Parameters.Add(

New SqlParameter("@.ID", 0))

TParam.Direction = ParameterDirection.Output

MyConn.Open()

MySQL.ExecuteNonQuery()

RID = TParam.Value

EndSub

<script/>

The server\ASPNET account has access on the server and to the bsld_demo db. I still get this error:

Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET'.

Source Error:

Line 26: Line 27:Line 28: MyConn.Open()Line 29: Line 30: MySQL.ExecuteNonQuery()

Source File:D:\SOS\ConnectionTest\Default.aspx Line:28

Stack Trace:

[SqlException (0x80131904): Cannot open database requested in login 'bsld_demo'. Login fails.Login failed for user 'server\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735251 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.default_aspx.Button1_Click(Object sender, EventArgs e) in D:\SOS\ConnectionTest\Default.aspx:28 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

What do I do now? I have tried everything else. Thanks,.|||

You need to go back to my original post about how you create a SQL Server account for Asp.net because of the text below.

(Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET')

And clean up your connection string with the link below. Hope this helps.

http://www.carlprothman.net/Default.aspx?tabid=86

|||Thanks for the suggestions. I cleaned my string. However, I do have a SqlServer account for ASP.Net on the SQLServer. I guess that's why I am confused. In SQLServer I have an account set up as 'server\ASPNET' with server being my server name. I granted that user access to the bsld_demo db.|||

No each instance of SQL Server is a separate server permissions so check the Master database of the instance you are trying to run the application to see if Asp.net account was created in Syslogin table, if it is there try to connect to it if you cannot do that then the permissions is orphaned, that is covered in the second link, it usually involve editing the Master, most places I have worked this require a lot of Management signatures.

http://msdn2.microsoft.com/en-us/library/ms178593.aspx

http://msdn2.microsoft.com/en-us/library/ms175475.aspx

Here is Microsoft covering remote connection see if that makes a difference. Hope this helps.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Thanks Caddre. I tried the links and suggestions and nothing worked. I even did the following: I turned Anon access off and tried to log in and got the same "Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails."
error. This time I got "Login failed for user 'AD\abcxyz'." which AD\abcxyz is my Network ID.

On thin I found interesting is that if I create the connection string in VWD, I can do datagrid functions like edit, delete and insert. But if I try to create a sub procedure and try to link the database that way, this is when I encounter the error. Do you or anyone else know of a way or some examples where I can create a button and code it to do the same thing as the default "Insert" feature in VWD? If I can find this, I'll avoid this issue.

|||

EDIT

I am glad you posted this database was VWD created and that makes it a user instance database, you can fix the problem by backing up the database and restore and it will become regular database. The link below covers it. Post again and let me know how it goes.

http://msdn2.microsoft.com/en-gb/library/ms143684.aspx

|||Hey Caddre. I tried the suggestion in the link and nothing. I thought I would try and create a tbl in another db and point the page to it. I get the same error. I'm almost tempted to reload the .net framework to see if that fixes it but I don't want to screw up the other instance. This is just the craziest thing I have ever seen. You have any other suggestions? You have been awesome in helping just so you know.. :-)|||

EDIT

Try backup and restore first, then the framework and this is from Microsoft support you should change from Windows Authentication to Mixed authentication

http://support.microsoft.com/kb/555332

AS2005 Member Properties - MDX Query

Hi,

I am having a bit of a problem with retreiving member properties from our AS2005 cube. I'm using MDX query's in a VBA Excel app via ADO (not ADOMD). I've been prototyping the queries in the SQL Server Management Studio.

I currently have the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This works fine, however I now want to CrossJoin the measures [CR 01 Net Skw Adj USD] and [JTD 01 USD] with a dimension called CS Tenor in order to give me a seperate value for each Tenor.

So far the only way I can do this is with the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} * {[CSTenor].[CS Tenor].MEMBERS}

ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This has the unfortunate side effect of CrossJoining the Portfolio member property with Tenor as well, resulting in 9 unwanted Portfolio columns (there are 9 members in CS Tenor). In reality the problem is more severe as I have around 10 member properties in the query, I reduced it to 1 here for simplicity.

My question is: Can I bring back member properties on the Rows axis? and a seperate question is: Can I CrossJoin some measures and not other on the columns axis, for example, could I CrossJoin [CR 01 Net Skw Adj USD] with CS Tenor, but not [JTD 01 USD] ?

Many thanks!

I don't believe you can cross join some measures and not other on the column axis but assuminng portfolio is an attribute of your Trade dimension the you can try this on your rows axis:

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso],strtomember("[Trade].[Portfolio].[" + [Trade].[Trade By Source System].CurrentMember.Properties("Portfolio") + "]") ))

Hope this helps.

V

|||

Actually, you should be able to do what you want on the column axis by simply constructing the full set of tuples that you want back. Here's an example from Adventure Works:

select

{ [Date].[Calendar Year].Members } on rows,

{ { [Measures].[Internet Sales Amount] } * { [Product].[Category].Members }, ( [Measures].[Internet Gross Profit], [Product].[Category].[All Products] ) } on columns

from

[Adventure Works]

In this query, I'm just pulling back calendar years on rows. On the columns, I'm pulling back the [Internet Sales Amount] measure cross joined with the product categories along with the [Internet Gross Profit] measure -- but with it coming back only with [All Products], not all the product cateogries. The only caveat is that all the tuples returned have to have the same dimensionality. Thus the need to return [Internet Gross Profit] with a member (in this case, the [All Products] member) of the same hierarchy used in the cross join that generates the first set of tuples.

In your case, you'd want something like this (not knowing exactly what your [All] member is, I just called it [All CS Tenors]):

{ { [Measures].[CR 01 Net Skw Adj USD] * [CSTenor].[CS Tenor].Members }, ( [Measures].[JTD 01 USD], [CSTenor].[CS Tenor].[All CS Tenors] ) } on columns

HTH,

Dave Fackler

|||

Hi,

Sorry for the delay in replying, I've been on vaction.

Many many thanks for this solution, it works perfectly!

Happy New Year,

Stuart

AS 2000 with 64 bit OS

I moved one of my AS 2000 olap db new server -Windows 2003 "64-bit" OS with lot more processor then old server "32-bit" Windows OS.

Good news is my cube processing I save 50% time but my query performance is slow, I was thinking my query should fly.

I have AS 2000 "32-bit" in "64-bit" Windows server. I have same settings in AS 2000 as old server.

Thank you - Ashok

Well, first of all, if you running IA 64, you should install 64bit version of Analysis Services and run natively.

Second, running on higher end machine doesnt neccessarily means single query performance is going to improve drastically. With higher-end hardware, you should get higher throughput, ability to handle larger databases.
For single query you might be way better off creating additional aggregations supporting your query patterns. With processing times improving you should be able to afford having higher persentage of aggregations that should improve perofmance.

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