Problem getting some users to be able to run reports (probably security of some sort)

I am able to deploy and run reports from my screen at work, but some non-developers cannot run them. They can see the reports, but get the following error message when they click on the report:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

--

--

- I assume the preceding error has something to do with credentials. I am using Windows security authentication for this. One user has worked through this and is now getting cannot run report query (one of the report queries in the report). Help would be appreciated.

Can you make sure that the users who are having issues can actually connect to the SQL Server & database?

Have you tried running Profiler to see what credentials are actually passed to the SQL Server when the user runs the report?

Jarret

|||What was found was a security issue. I got some DBA help in setting up permissions to login to the database and to grant run permissions on the queries. First access was granted to the database itself, the we had to go into security on the database and setup the user and grant permissions. This resolved the problem.

Problem getting rid of nondistinct records

Hello,

I have a problem with which I need some help. I have a table called "user" that contains the userid, name, and address. However for users with multiple listed addresses, there are duplicate records...example:

userid, name, address:

0001, John Smith, 123 main rd.
0001, John Smith, 456 second rd.
0002, Bob Jones, 435 another rd.

I need to combine all the duplicate records into a single entry by concatenating the adresses. (I.E. "0001, John Smith, 123 main rd/456 second rd")

I can select the duplicate rows by doing:

SELECT * FROM user
WHERE userid in (
SELECT userid FROM user
GROUP BY userid
HAVING COUNT(*) > 1 )
ORDER BY userid ASC

Does anyone know how I could concat all the duplicate rows into one and then delete the duplicates? I dont care which order the addresses are concatinated in. Any ideas?

Thanks in advance,
Andrei GirenkovFor normalization concerns you should AVOID to do what you ask. A better solution, but requires you to modify the architecture of your database, is to make an external address table:

User Table:

0001, John Smith
0002, Bob Jones

Address Table:

0001, 123 main rd.
0001, 456 second rd.
0002, 435 another rd.

To solve your problem, anyway, i think you have no other way to use cursors, since the number of equals row you need to concatenate will vary.|||Thanks for the reply, but I already solved the problem. I ended up doing it with for loops. It took a long time to execute but it's a one time operation.

~Andrei|||Originally posted by Andrei_Girenkov
Thanks for the reply, but I already solved the problem. I ended up doing it with for loops. It took a long time to execute but it's a one time operation.

~Andrei

This could be for you or anyother reader.. its an example I put together that would show you efficient ways to point out duplicate records from within a database table. Also applies to most relational database that support the Structured QUery language (SQL)

create table tmp_awahs_dupes_killer
(
record_id varchar(100),
name varchar(100)
)
go
insert into tmp_awahs_dupes_killer values(100, 'In')
insert into tmp_awahs_dupes_killer values(200, 'These')
insert into tmp_awahs_dupes_killer values(300, 'Rows')
insert into tmp_awahs_dupes_killer values(400, ',')
insert into tmp_awahs_dupes_killer values(500, 'These')
insert into tmp_awahs_dupes_killer values(500, 'Are')
insert into tmp_awahs_dupes_killer values(500, 'The')
insert into tmp_awahs_dupes_killer values(500, 'Dupes')
go

select * from tmp_awahs_dupes_killer t1 where (select count(t2.record_id) from tmp_awahs_dupes_killer t2 where t2.record_id= t1.record_id)> 1

drop table tmp_awahs_dupes_killer
go|||Manowar is right. Your database is already denormalized enough. I predict a post six months from now requesting help parsing all those concatenated addresses into separate elements again.

awahteh: I suspect your query would be more efficient using Andrei_Girenkov's linked subquery method, though the optimizer might convert your syntax to this plan prior to execution anyway. If it doesn't, then you will end up executing your subquery once for every record in your main table, while Andrei_Girenkov's method only runs the aggregate once.

blindman

problem for Security in Sql 2005 Express.

hi
i am using vc# 2005 and sql 2005 express.
my problem is does exist any way to set password on sql2005 database ?
(because i don't want to end users access to sql server's tables and datas and ...)
how to solve my problem ?
thanks

Hi Hamed

You can download Microsoft SQL Server Management Studio Express and you can create users there for your database.

Regards,

Pradeep

|||

Hi,

You don′t set password on databases. You create logins on the server level (server principals) which relate to either a Windows Account (Windows Authentication) or a SQL Server account (SQL Server authewntication). These principals can be either in a overall server role and / or be granted access to individual databases with database principals. these database principals can be granted individual right on the objects within the database (securables) or can be assigned to database roles.

So, first create a server login for an indivudal user or a Windows groups, then grant the appropiates right either individually or through the role membership within the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

problem converting string to integer

Hi, I need to convert the values entered into textboxes by the users before I can insert into db.

have tried the following.

Dim eventnum As string 'tried using integer makes no difference what i declare the variable as

eventnum = Convert.ToInt32(txteventnum.Text)

This value needs to be inserted into the field event_number wich is datatype int

I get the following error message when I try to insert.

Conversion failed when converting the varchar value 'eventnum' to data type int.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'eventnum' to data type int.

Source Error:

Line 62:
Line 63: ' Execute(query)
Line 64: myCommand.ExecuteNonQuery()
Line 65:
Line 66: 'Close the connection

Source File: C:\Inetpub\loans\MemberPages\Request.aspx.vb Line: 64

Stack Trace:

[SqlException (0x80131904): Conversion failed when converting the varchar value 'eventnum' to data type int.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
 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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
 Request.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\loans\MemberPages\Request.aspx.vb:64
 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

based on where your error is occurring, i suspect that the value from the textbox was successfully converted to an integer. This leads me to think that there's something wrong with how your query is constructed. Please post the code for constructing the query.

|||

thanks for the reply here is the code for query.

'Connection String value
Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString

'Create a SqlConnection instance
Using myConnection As New SqlConnection(conn)
myConnection.Open()

' Specify the SQL query
Const sql As String = "insert into requests ( [User_Name], [NHI], [Event_Number], [ACC_Number], [Request_Date]) values ('username','nhi','eventnum','accnum','reqstdate' )"
', [Required_Date], [NHI], [Event_Number], [ACC_Number], [Request_Date]
'Create a SqlCommand instance
Dim myCommand As New SqlCommand(sql, myConnection)

' Execute(query)
myCommand.ExecuteNonQuery()

'Close the connection
myConnection.Close()

End Using

|||

resolved it was the query string

was

Const sql As String = "insert into requests ( [User_Name], [NHI],[Event_Number], [ACC_Number], [Request_Date]) values('username','nhi','eventnum','accnum','reqstdate' )"

now

Dim sql As String = "insert into requests ( [User_Name], [required_date],[NHI], [Event_Number], [ACC_Number], [Request_Date]) " & _
"values ('" & username & "','" & reqrddate & "','" & nhi & "'," & eventnum & ",'" & accnum & "','" & reqstdate & "' )"

But thanks for replying

|||

glad you're all set.

But, please read up onSQL Injection Attacks
A parameterized query helps protect from sql injection and resolves issues related to quoting.
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

Problem connecting to SQL-Server

Hello and thanks for reading.
This is my environment:
3 CITRIX Servers with anonymous users to login
Win2K Server with MS-SQL2K and the databases
Users logging in from the first server can connect to the sql-server, the
users logging in on the two other servers can´t. I think the reason is that
the anoymous users from the latter 2 servers have no right to connect to the
DB-Server. I think it is a problem with the windows users security, not with
the SQL-server´s. But I can´t figure out how to change this.
Any idea?
Thank you!
BerndI could tell you how to do this but then I would have to kill you...
But seriously
You will need to add some sort of security in your SQL Server database.
This can be in two forms.
Either Server 2000 security or SQL Security.
So the question is this.
When your CITRIX Servers users log into the Windows server do they have a particualr domain, or are they assigned any sort of proxy ID for use on the Server 2000 ?
If the answer is yes then this domain or ID can be used is SQL Server Secrurity.
If the answer is no then you will need to create each user (or CITRIX Servers application) individually within SQL Server security, assigning it User ID and passwords.
If you have any further questions then please do not hesitate to ask.
Peter Nolan
MCP
>--Original Message--
>Hello and thanks for reading.
>This is my environment:
>3 CITRIX Servers with anonymous users to login
>Win2K Server with MS-SQL2K and the databases
>Users logging in from the first server can connect to the sql-server, the
>users logging in on the two other servers can=B4t. I think the reason is that
>the anoymous users from the latter 2 servers have no right to connect to the
>DB-Server. I think it is a problem with the windows users security, not with
>the SQL-server=B4s. But I can=B4t figure out how to change this.
>Any idea?
>Thank you!
>Bernd
>
>.
>|||Maybe I was not clear enough:
1) My application connects to the MS-SQL instance using SQL security.
User/Password inside SQL-Sever and does not exist outside.
2) MS-SQL Instance is located on Server DB
3) CITRIX Terminalserver is located on SERVER CT1, CT2, CT3
4) Users log on CT1 - CT3 using windows anonymous users and start the
application (wich tries to connect to the db using the SQL-user)
5) On CT1 this succeeds, CT2-CT3 this fails.
Cheers
Bernd
---
This can be in two forms.
Either Server 2000 security or SQL Security.
So the question is this.
When your CITRIX Servers users log into the Windows server
do they have a particualr domain, or are they assigned any
sort of proxy ID for use on the Server 2000 ?
If the answer is yes then this domain or ID can be used is
SQL Server Secrurity.
If the answer is no then you will need to create each user
(or CITRIX Servers application) individually within SQL
Server security, assigning it User ID and passwords.
If you have any further questions then please do not
hesitate to ask.
Peter Nolan
MCP
>--Original Message--
>Hello and thanks for reading.
>This is my environment:
>3 CITRIX Servers with anonymous users to login
>Win2K Server with MS-SQL2K and the databases
>Users logging in from the first server can connect to the
sql-server, the
>users logging in on the two other servers can´t. I think
the reason is that
>the anoymous users from the latter 2 servers have no
right to connect to the
>DB-Server. I think it is a problem with the windows users
security, not with
>the SQL-server´s. But I can´t figure out how to change
this.
>Any idea?
>Thank you!
>Bernd
>
>.
>