Active Directory/ADSI connection problems...Here's the correct syntax

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

|||

Hi,

Not sure if this is the correct forum but I also need some help on a similar matter. I have connected my Sharpoint Server to ADAM and imported the users and groups into Sharepoint, however I want to display some SQL 2005 data in a web part, how can I get the ADAM user to be recognized by SQL 2005 using the current logged in Sharepoint (ADAM) User.

Jos

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory/ADSI connection problems...help!

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory Service And IIS Problem

I have a ASP.Net Project to read active directory usernames.

Its working fine when running thru asp development server.

But while running thru IIS Server. It displays the following error

"An exception of type 'System.DirectoryServices.DirectoryServicesCOMException' occurred in System.DirectoryServices.dll but was not handled in user code
Additional information: Logon failure: unknown user name or bad password."

This is my Code :

Public Function readADSusers()
Dim enTry As DirectoryEntry = New DirectoryEntry("LDAP://spiretek.local")
Dim mySearcher As DirectorySearcher = New DirectorySearcher(enTry)
mySearcher.Filter = ("(objectClass=user)")
Dim resEnt As SearchResult
For Each resEnt In mySearcher.FindAll()
Select Case myKey
Case "samaccountname"
TextBox1.Text += myResultPropColl(myKey)(0)
End Select
Next
End Function

Please Help Me......This is not the right forum for this, but you will have to provide network credentials (either the current the process is running with or custom ones) to access the active directory.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

AccessViolationException from SQLDataSource

I'n getting a randomAccessViolationException during the select operation of an SqlDataSource (ASP.NET 2.x), after I have modified the CommandText from it'sSelecting event.

I know it's considered a bad idea to dynamically modify the SQL rather than use parameters, but in this instance, I'm more concerned as to why this exception is occurring. It *only* happens if I modify the CommandText and even then I'd say it only occurs 10% of the time. More annoying than anything is that it is returning the correct results. I can catch the exception from theSelected event and the stack trace reveals that the failing method isSQLExecDirectW.

There is nothing wrong with the CommandText syntax and MS documentation states that it can be changed from the Selecting event, so I'm at odds as to why this is happening.

For completeness, the reason I can't use parameters is that I'm using the ODBC Datasource with a Paradox database (Intersolv drivers). I need an optional predicate and IsNull() is not supported, and I cannot get the {fn IFNULL(?, Table.`Field`)} escape sequence to work with an SQL input parameter.

Can anyone put me out of my misery?

This is an exception throwned from unmanaged code because something is trying to access protected memory so it sounds like the database drivers.

Have you checked for updated drivers ? You should probably report the bug to the people providing these drivers.

Accessing the ReportService.asmx via an ASP.NET page

Hi all, I have an ASP.NET page which I want to use to be able to upload
*.rdl documents to the report server "on the fly".
When I call the webservice via some code in my ASP.NET webpage I receive the
following error
"The request failed with HTTP status 401: Unauthorized."
Any ideas more than welcome..
TIA MarkHi all, a bit of research produced the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811318
All fixed and working now :)
Cheers
Mark
"Mark" <dont@.spam.me> wrote in message
news:ud3zhsenFHA.3408@.tk2msftngp13.phx.gbl...
> Hi all, I have an ASP.NET page which I want to use to be able to upload
> *.rdl documents to the report server "on the fly".
> When I call the webservice via some code in my ASP.NET webpage I receive
the
> following error
> "The request failed with HTTP status 401: Unauthorized."
> Any ideas more than welcome..
> TIA Mark
>

accessing sql7 database using ADO and ASP web page.

Hello all!

I am kinda new to using sql 7.0 and am trying to set up a web page that will access a sql7 database. I have a database with several tables in it created on my machine. My asp page uses a connection string that looks like:

"strConnect = "Provider=SQLOLEDB; PersistantSecurity Info= False; User ID = sa; password = 1234; Initial Catalog=equip;Initial file Name =c:\mssql7\data\tracking_data.mdf""

When I try to load this page my web browser displays the "page cannot be displayed error" and shows an error type of

"Microsoft OLE DB Provider for SQL Server (0x80004005)
Device activation error. The physical file name 'c:\mssql7\data\tracking_data.mdf' may be incorrect.
/tracking/rptsvrc.asp, line 22"

Line 22 of the page rptsvrc.asp contains the code:
"objCommand.ActiveConnection = strConnect"
I am at a lose as to why this doesn't work. The file name and path are correct and they do exist.

ANy suggestions here?

Thanks CVC505Your connection string should be something like this:

"provider=SQLOLEDB;server=SQLServerName;uid=userid; password=password;database=DatabaseName;Trusted_Co nnection=No"

DatabaseName points to the logical db name not the filename.

Hope this helps...|||Thanks for the suggestion but it still does not work. I have changed my programs to that list 23 reads:

objConn.Open "Provider=SQLOLEDB; Server=rick2; User ID = sa; Database = equip; Trusted_connection = no"

Where rick2 is the server name ( I changed servers for the weekend)
equip is the database name as it appears in the EM list.

and line 24 is now:
objRS.Open "user1", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

where user1 is the table name in the equip database.

When I try to make the connection now I get an error that says:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name 'user1'.
/tracking/rptsvrc.asp, line 24

This has me stumped because it will work about once then quit.
One other thing I have noticed; when I created this database in the EM, I had named it Tracking ( In the data subdirectory the file is named tracking_data.mdf). The first time I ran the program the name in the EM changed to equip. ( still is named Tracking_data.mdf in the dat subdirectory). I am so new to this I don't have a clue what to look for.
cvc505|||Your connection seems to be working now but it is not finding the table user1.

Try changing you recordset open to a simple query:

'SELECT * FROM syscolumns'

See if that returns anything.

You can also try prefixing user1 with its owner. I.E. dbo.user1

Accessing SQL2K on the net from local pc

Hi, i'm new to SQL2K. I see in my connect string (ASP) a IP-address. I can
choose between a public or a internal IP.
If i use the public IP, where can i find examples on how to query the
database from a local pc in a Visual Basic 6 program ? Or is this not
possible?
Grard.Hi
You should consult your network administrator for the correct IP address to
use. It is quite possible that there are several NICs on the database and we
b
servers.
John
"Gérard Leclercq" wrote:

> Hi, i'm new to SQL2K. I see in my connect string (ASP) a IP-address. I can
> choose between a public or a internal IP.
> If i use the public IP, where can i find examples on how to query the
> database from a local pc in a Visual Basic 6 program ? Or is this not
> possible?
> Gérard.
>
>|||I ask if its possible, not which IP to use. And if its possible, some
examples.
Grad.|||Hi
If you are looking for example connection string try
http://www.connectionstrings.com/ or
http://www.carlprothman.net/Default.aspx?tabid=81
John
"Gérard Leclercq" wrote:

> I ask if its possible, not which IP to use. And if its possible, some
> examples.
> Gérad.
>
>

Accessing SQL Svr 2000 on NT4 box

We have recently upgraded our web server to Windows 2000 and loaded the .net platform. I have starte creating ASP.net programs, but our database server that houses the Sequel Server 2000 database is running on NT4. We are waiting for the money to be approved for a new database server with Windows 2000. Is it OK to be accessing sequel server on a NT4 machine from an ASP.net program? I have the connection to the server set up in the web.config file.

This database server hung up for no apparent reason this weekend and had to be rebooted. Only I have access to the new ASP.net pages and was testing them for the first time this last friday.

Thanks for your help!
JimHi Jim,

Sure, it's fine. I run ASP.NET apps that access NT boxes with no problem. I'd love to upgrade them, but such things are not always under my control, alas.

Don

Accessing SQL Server 2000 with ASP.NET

Hi Everyone,

I just started using ASP.NET and I cannot figure out how to use a connection string to connect to a SQL Server database using ASP.NET. I want to use Windows Authentication to bring up a database called upsizedCandidate on a server called SQLSERVER and display a table within that database through internet explorer. I'm not sure if I should be using OLE DB or ODBC. Can anyone help me with this? Thanks in advance!

There is an easy way to set this up.

1. Create a new webForm.

2. Go to view -> Database Explorer.

3.In the new window, make a new connection to your database. Fill in the relevant parts.

4. In the Database explorer, find a table/view that you wish to display.

5. Drag and drop the view/table onto the page. This will setup everything for you. Then you can edit the sqlDataSource and Gridview/Datagrid accordingly.

HTH

Jag

|||

toddw607:

I cannot figure out how to use a connectionstring to connect to a SQL Server database using ASP.NET. I want to useWindows Authentication to bring up a database calledupsizedCandidate on a server called SQLSERVER

Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=SSPI;

remember whenever you stuck with connection string go to http://www.connectionstrings.com/ really helpfull stuff.

toddw607:

I'm not sure if I should be using OLE DB or ODBC. Can anyone help me with this? Thanks in advance!

look,It depends on what database are you using, if you are using Sql ServerI would recommend you to use SQLClient namespace of .netframework, if you find specific driver for database always usethat at prior level, other wise go for oledb or odbc.

cheers

|||

Hi Jag,

Thanks for the quick response! I did what you instructed to do but when I try to bring it up in the browser it gives me the following error:

The connection name 'upsizedCandidateConnectionString1' was not found in the applications configuration or the connection string is empty.

Here is my code:

<%@.PageLanguage="VB" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><tableborder="1"class="ex"width="100%"><tr><td><pre>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataSourceID="SqlDataSource1"EmptyDataText="There are no data records to display."><Columns><asp:BoundFieldDataField="myNewCol1"HeaderText="myNewCol1"SortExpression="myNewCol1"/><asp:BoundFieldDataField="myNewCol2"HeaderText="myNewCol2"SortExpression="myNewCol2"/></Columns>

</

asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:upsizedCandidateConnectionString1 %>"ProviderName="<%$ ConnectionStrings:upsizedCandidateConnectionString1.ProviderName %>"SelectCommand="SELECT [myNewCol1], [myNewCol2] FROM [ToddsTable1]"></asp:SqlDataSource></pre></td></tr></table>

</div></form>

</

body>

</

html>

Any thoughts? Thanks again!

|||

When I drag the table over another form is created called <web.config> which has my connection string in it but is not referenced from my code. Any thoughts?

|||I just put the provider name

ProviderName

="<%System.Data.SqlClient%>"and connection string

ConnectionString

="<%Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=True%>"

into my code and now I'm getting a different error:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

Any thoughts?

|||

When you drag and drop the table, visual studio automaticallycreates a connection string in a file called web.config. This is thesafest location for a connection string. It then creates ansqldatasource that is used to connect to the database, and a gridviewto display the data.

The sqldatasource should be referencing the connection string. Have a look in the web.config file and get the name of the connection string. Then have a look at the properties of the sqldatasource to see where you are meant to put this. I don't have Visual Studio 2005 installed on the computer I am using at the moment, so I cannot tell you the exact property to look for.

Here is a page that should have everything you are looking for if you get stuck. It should not be hard though - just try deleting everything and dragging and dropping again.

Let us know how you get on.

Jag

|||Thanks for all the info! I dled all the ASP.NET gridview examples and saw how everything worked. I set it up like the examples and the main page works fine now when I access it through the server but I have one more problem: I have tables coming out through SQL Server 2000 and when I try to bring them up through the main page it gives me the 404 error. I have the web.Config file stored properly so that the main page can access it but I think I need another one stored else where so when I call the tables from the main page they will automatically retrieve from SQL Server. Any thoughts on the placement or if it could be another problem? Thanks.|||I forgot one more thing which may help...I have the pages saved which the tables are stored in from SQL Server 2000. All the main page does is reference the saved pages and is supposed to bring them up in Internet Explorer like the main pages does. These pages work fine if I go into my folder on the server and bring them up in VB and right click to "View in Browser", they come up in IE but but I attempt to bring them up by reference it gives me the 404 error in IE. I hate to be repetitive with some of the info but I wanted my problem to be as clear as possible before you read the whoel thing. Thanks again!

Accessing SQL Server

Hi Group,

I have my IIS webserver outside the fire wall and my SQL Server is behind the firewall . Can IIS Still access the SQL Server for ASP pages, is it for this situation do i need to open the port no 1433 in the firewall for SQL Server, if so is there any security threat?
Thank You for your Time
Krishna<sarcasm mode="on">
Security threat? This is Microsoft, you don't need to worry about security!
<sarcasm mode="off">

Sorry about that.

Yes, if you have a firewall and your IIS server is outside the firewall, then you will need to open up port 1433 to allow traffic from your web server back to your SQL server. You should be able to do this in such a way that the 1433 traffic MUST originate from the IP address of the web server.

Regards,

Hugh Scott

Notes:
1. Sorry again for the sarcasm, I couldn't overlook the opportunity.
2. You should be VERY cautious with your ASP pages. It would be far more secure to avoid any ADO references in the ASP pages. Instead, consider creating DLLs that embed the ADO and expose properties and methods that you can access from ASP.
3. Be sure to avoid putting connection information in your ASP. At a minimum, try putting the connection info in the global.asa file or in include files that are in a directory with tighter security.
4. Finally, be sure than you "cleanse" user input of any characters that might be used in a SQL injection attack ("%" "," "'" ";"). there are other characters, but these are the bigger ones.

Originally posted by mellamarthy
Hi Group,

I have my IIS webserver outside the fire wall and my SQL Server is behind the firewall . Can IIS Still access the SQL Server for ASP pages, is it for this situation do i need to open the port no 1433 in the firewall for SQL Server, if so is there any security threat?
Thank You for your Time
Krishna

Accessing reportviewer from web

Hi,
apologies for what I know is a very basic question. We have a very simple ASP.NET page that includes a report viewer object. The site containing the page is on a public server, and the report server is on an internal server (actually the same physical machine, but running on a different IP) - we are referencing the report server via a 10.x.x.x address and it's not connecting from outside. The report server is not accessible from the public address, and I don't believe there's firewall issues - can someone please point me in the right direction?

thanks,
Steve.

<snip>
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Security;

namespace WebReportTest2
{
/// <summary>
/// Summary description for WebForm2.
/// </summary>
public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlForm Form1;
protected System.Web.UI.WebControls.DropDownList DropDownList1;
protected System.Web.UI.WebControls.Label Label3;
protected System.Web.UI.WebControls.Label Label4;
protected System.Web.UI.WebControls.DropDownList Dropdownlist2;
protected System.Web.UI.HtmlControls.HtmlForm Form2;
protected System.Web.UI.WebControls.DropDownList Dropdownlist3;
protected System.Web.UI.WebControls.Button btnLogOut;
protected Microsoft.Samples.ReportingServices.ReportViewer ReportViewer1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
//this.DropDownList1.Items.Add(new ListItem("report1,"report1"));

string param = DropDownList1.SelectedValue;
ReportViewer1.ReportPath = "/Development/report1" + param ;
ReportViewer1.ServerUrl = "http://10.72.x.x/reportserver";

}
If you log onto the console of the web server are you able to access the report server using the 10.x.x.x address? My guess would be that it is an IP/subnet issue, but I'm not really an expert in that area.|||Yep, it's actually the same server but sites running on different addresses. It looks to me like the reportviewer object is being created on the client side, and therefore can't resolve the internal 10.x.x.x address. It's not my code, and I'm no ASP.NET guru, but the object seems to be created in the ASPX page correctly, with "runat=server", but is then also created in a C# code behind page, and it's this object that's actually being created, and it's client, not server side...if all that makes sense...

cheers,
STeve.
|||

I have not done that much with the report viewer in ASP.NET, most of my experience with this control has been with the Windows Forms edition. But my understanding with the ASP.NET version was that most of the rendering takes place on the server side.

One thing thing you could checked is the permissions. If you are running the ASP.NET app from a public server I assume that you are running the standard impersonation mode which would mean that the account accessing the report server would either be the ASPNET account if you are running IIS 5 or the account that is configured for the app pool if you are running IIS 6. The account in question would need to have permission to access the report server and run the report(s) in question.

accessing reports securely through ASP

Hej
I'm new to Reporting Services. I'm using RS 2005, ASP 2.0 and VS 2003.
I've created the reports and have been using a URL link in my ASP site to
ReportServer/Pages/ReportViewer.aspx?%2fFOLDER%2fREPORT for viewing the
reports. When the reports are accessed outside the server, the browser
requires username and password. This isn't desired.
Whats the best way to handle this? If I switch to VS 2005 does the
ReportViewer control handle this? Is it possible/safe to change the user
rights for ReportViewer.aspx? Its important not to allow users access to the
Report Manager, but can the rights to viewing individual reports be handled
through there?
I appreciate any advise on the matter.On Mar 14, 2:27 pm, jknurek <jknu...@.discussions.microsoft.com> wrote:
> Hej
> I'm new to Reporting Services. I'm using RS 2005, ASP 2.0 and VS 2003.
> I've created the reports and have been using a URL link in my ASP site to
> ReportServer/Pages/ReportViewer.aspx?%2fFOLDER%2fREPORT for viewing the
> reports. When the reports are accessed outside the server, the browser
> requires username and password. This isn't desired.
> Whats the best way to handle this? If I switch to VS 2005 does the
> ReportViewer control handle this? Is it possible/safe to change the user
> rights for ReportViewer.aspx? Its important not to allow users access to the
> Report Manager, but can the rights to viewing individual reports be handled
> through there?
> I appreciate any advise on the matter.
I believe that there is a way to control (minimally) access to reports
in the Report Manager interface (check the top right-hand side of the
main page). Also you can control user access through the application
(as far as, not showing the user a link to the Report Server, etc).
Sometimes the login is required when the user accesses the Report
Server from Mozilla or Firefox (whereas, w/Internet WExplorer, it may
not happen). Also, you can check the Directory Security for the
Reports and Report Server Virtual Directories in IIS to check for
Windows Authentication, anonymous access, etc (via right-clicking My
Computer, select 'Manage' and go to Services and Applications ->
Internet Information Services -> Web Sites -> Default Web and right-
clicking the Reports or ReportServer Virtual Directory and selecting
Properties and the 'Directory Security' tab). Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Accessing Report Parameters Class through Web Service

I am using ASP .NET along to render my reports. I've extended the web
service class such that I specify the report folder, and I get all
reports and their respective parameters. I then dyncamically create
the controls (such as datepicker, textbox, etc...) based on the
Parameter Name, and Type. The drop down list is the one giving me the
hardest time though.
My problem is this: I have a Report with 3 parameters. The first 2
are dates. I can retrive all info on them just fine. The name,
prompt, and type (datetime). The third parameter (AppUserKey) however
is a dropdown list of all the users. I wrote a stored procedure that
does this called spGetAppUsers. Here is the structure of this
parameter as per the RDL file:
</ReportParameters>
...
...
<ReportParameter Name="AppUserKey">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>AppUserKey</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
<LabelField>AppUserName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
I can get the Name, and even from the ValidValues, the ValueField and
LabelField, however, I want the <DataSetName> entry.
This is so that after I dynamically create the drop down list, I'm
able to populate it via the stored procedure. Recall that the
ReportParameter class is defined here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_ir_5zec.asp
ReportParameter::QueryParameter is a boolean property. Doesn't
actually return the name. Just whether or not you are using a query.
ReportParameter::DefaultValues is a string but is empty, since the
report hasn't executed the stored procedure (makes sense).
I want this to be a generic solution. Hence all this trouble to get
all the "givens" from the Report, and to reconstruct it in ASP .NET.
I need that DataSetName!!! :-S
Any help is appreciated.
Thanks for your time,
RoyThere are 2 ways to do what I want:
1. Instead of trying to get the stored procedure of my query based
parameter, I should've set forRendering = True in my
GetReportParameters function. That will actually return the query
based parameter set. Then I can populate the drop down list with that.
2. Load the .RDL (report definition in XML) in memory and use XPATH to
get the name of the parameter:
Dim reportDefinition As Byte() = Nothing
Dim doc As New System.Xml.XmlDocument
Try
reportDefinition = rs.GetReportDefinition(ReportsFolder &
strReportName)
Dim stream As New MemoryStream(reportDefinition)
doc.Load(stream)
In this scenario, assuming you have forRedering=True or False, you
still need to know the name of the parameter. So you'ld probably get
then name of the parameter and then check the value of QueryParameter
to see if it's true.
Then base your XPATH query on the following XML:
"/ReportParameter/ValidValues/DataSetReference/DataSetName[@.text]"
Or something similar. I forget. The above is proabably wrong and
could be written in a million different ways.
<ReportParameter Name="AppUserKey">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>AppUserKey</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
<LabelField>AppUserName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
Roy Assaly wrote:
> I am using ASP .NET along to render my reports. I've extended the
web
> service class such that I specify the report folder, and I get all
> reports and their respective parameters. I then dyncamically create
> the controls (such as datepicker, textbox, etc...) based on the
> Parameter Name, and Type. The drop down list is the one giving me
the
> hardest time though.
> My problem is this: I have a Report with 3 parameters. The first 2
> are dates. I can retrive all info on them just fine. The name,
> prompt, and type (datetime). The third parameter (AppUserKey)
however
> is a dropdown list of all the users. I wrote a stored procedure that
> does this called spGetAppUsers. Here is the structure of this
> parameter as per the RDL file:
> </ReportParameters>
> ...
> ...
> <ReportParameter Name="AppUserKey">
> <DataType>Integer</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>spGetAppUsers</DataSetName>
> <ValueField>AppUserKey</ValueField>
> </DataSetReference>
> </DefaultValue>
> <Prompt>AppUserKey</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>spGetAppUsers</DataSetName>
> <ValueField>AppUserKey</ValueField>
> <LabelField>AppUserName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> I can get the Name, and even from the ValidValues, the ValueField and
> LabelField, however, I want the <DataSetName> entry.
> This is so that after I dynamically create the drop down list, I'm
> able to populate it via the stored procedure. Recall that the
> ReportParameter class is defined here:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_ir_5zec.asp
> ReportParameter::QueryParameter is a boolean property. Doesn't
> actually return the name. Just whether or not you are using a query.
> ReportParameter::DefaultValues is a string but is empty, since the
> report hasn't executed the stored procedure (makes sense).
> I want this to be a generic solution. Hence all this trouble to get
> all the "givens" from the Report, and to reconstruct it in ASP .NET.
> I need that DataSetName!!! :-S
> Any help is appreciated.
> Thanks for your time,
> Roy

Accessing MS SQL DB on own server?

Hi

If my site is hosted with an external company web host can I use ASP.NET pages to connect to a MS SQL DB that is on my company's internal server if I had MS SQL installed on it?

My company's server is always on and always connected to the internet.

I have access to my company's server if I need to set anything up on it for this to happen.

There will not be any kind of overload as the site is not accessed constantly.

Also, would this cause any kind of security leak? If so how would I go about plugging this hole?

Our server does have confidential info on it.

Cheers

kefi2927Since you haven't received a reply I'll post my thoughts. As someone who knows little about networking and security I personally would not do this, although it would be possible. Exposing a server to the internet is not something to be taken lightly, and will require diligence in keeping up with patches and security issues.

My 2 cents,
Terri