Using Integrated Security from .Net Web to access SQL Server 2005

SQL Server Security

Index ‹ SQL Server

- Previous
  - 1
    - An OLE DB error has occurred - Description: "Class not registered" Hi I have a package the connection of which is defined in a config file. When the source is on SQLserver, the package executes fine but when i change the connection to Oracle then i am getting this error. TITLE: Microsoft Visual Studio ------------------------------ Error at DMND_PROBS_EXTRACT [Connection manager "DLWSDV18.PM"]: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Error at DMND_PROBS_EXTRACT [DEMANDPROBLEMDETAILS [1]]: The AcquireConnection method call to the connection manager "DLWSDV18.PM" failed with error code 0xC0202009. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------ Please let me know if theres any solutions. Thanks, Vipul Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 2
    - 1 Flat file to 2 destination tables with different mappings :Please Help Here is the Scenario. I have a flat file with name " Employee.txt " (Full url: C:\Employee.txt ) .The File content is like this Anil,Engineering,1997 Sunil,Sales,1981 Kumar,Inventory,1991 Rajesh,Engineering,1992 ( Note: Items are Comma Seperated ) Now, i have a SQL Server database called " EmployeeDB" which has 2 tables " TblEmp1 ", " TblEmp2". The Table is like this. TblEmp1 : Columns EmpName EmpDept EmpjoinDate TblEmp2 : Columns EName EDate Edept using integration services (SSIS) i need code(vb.net or c#) to Create a dtsx package so that i can push the flat file content to these 2 tables. And the condition is : After Executing the package Data loaded in TblEmp1 should be like this EmpName EmpDept EmpjoinDate Anil Engineering 1997 Sunil Sales 1981 Kumar Inventory 1991 Rajesh Engineering 1992 (No change in order compared to source) And Data loaded inTBLEmp2 should be like this EName EDate Edept Anil 1997 Engineering Sunil 1981 Sales Kumar 1991 Inventory Rajesh 1992 Engineering Now, i know that we need to do like this in wizard 1) Create a flat file source component. 2) Create flat file connection and set the properties of flat file (delimeters and other things) 3) Create a Multicast Component. 4) Create a Path between Flat file source and Multicast. 5) Create 2 destination component(each for a table). 6) Create path from multicast to 2 destination components 7) Create a OledbConnection and set table names for 2 destination components.. 7) Now,i have to do mapping for destination1. 8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1. I have done it in wizard.I need to do it through code and i know that its not complicated.Please find the attached file with this mail.i have attached a screen shot of how i have done in wizard.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!! Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 3
    - Algorithm to populate a table with finite value combinations I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually. For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail. What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this. If I need to submit this in another forum please let me know. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 4
    - What DataType is best ... float, double or decimal? Thank you everyone. I appreciate all of the great advice (and shop humor) . I will modify my floats to decimal. Luckily I thought that this problem might arise so i wrote down every table and stored procedure that is currently using float so i can make the change quickly. With that said...it is still a huge pain in the butt..but thats how the ball rolls i guess. 8 - ) Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 5
    - How do you make a Report Parameter Invisible??? Hi, I have 3 report parameters. If a user selects 'value1' from the first parameter list then parameter list 2 & 3 should be open for the user to make their selection. BUT if the user selects 'value2' from parameter 1 then only parameter list 2 should be open and parameter list 3 invisible. Is this possible Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 6
    - Documentation of RMO and Merge Replication It sits on a completely separate 4th server. Cheers, James Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 7
    - sp_send_dbmail generate ansi file attachment format James, Thank you for the update on this. We can't institute our large-scale notification system until this fix becomes live. I assume you can't comment on the estimated time of release of the actual service pack, as we are currently unwilling to install the CTP2 on production systems. Thanks again. -Dan Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 8
    - CUBE FAILING/HANGING Dear friends in my previous post i was told that analysis services 2005 does not suport "custom roll up" and hence misrcosoft had introduced 2 new functions "last non empty and first non empty" for semi additive measures. But whenever im trying to bring the semi additive measure in the browser window of my cube, the cube gets hung and i have to finally close it w/o getting any analysis....please help To clarify u can take the example of the measure "closing stock" where u want that across time dimesnion it should alwaz return the last member at the year,half year,quarter,month and day level while across other dimensions like product,customer etc it should be the normal aggregation. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 9
    - about update I am doing as provided in the link. What is happening here is 1.I am sending the same text file. First time if the table does not have records it will insert all the record. When i run the same package for second time.It is the same text file no changes made. when i use conditional spit it comest up with few records as update.Records already exists with no change. and what it does is it does not update the existing record but inserts a new bunch of records in the conditional split what the codition which i had give is this output name:fields change [Column 1] != LookUpSysId || [Column 0] != LookUpCeOrgCode || [Column 2] != LookUpMrn || [Column 3] != LookUpServiceDate || [Column 4] != LookUpPxCode || [Column 5] != LookUpPxCodeType || [Column 6] != LookUpPxDesc || [Column 7] != LookUpOrderingProviderCode || [Column 8] != LookUpOrderingProviderIinternalId || [Column 9] != LookUpLoc please let me know Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 10
    - printing error Hi, some of my application users doesn't have Admin Privileges to their machines. When they try to print the reports, its giving a message "Unable to load client print control". Please help me how to solve this problem Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 11
    - ODBC Connection Fails from cluster node This issue appears to be MDAC. Uninstalling and reinstalling SQL to enable clustering must have caused the problem. What I did to figure this out was change the connection from dsn to dsn-less and it worked. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 12
    - SQL Server 2005 - Building a development Server Hi, I would like advice regarding what hardware to use for a SQL Server 2005 for use in a development environment. I am the only developer and user of the database. I develop at home, and currently use one PC, to develop the software. My PC is Athlon XP 4000, 2 Gig RAM, 1 x 160gb HD . I am developing an asp.net website along with a variety of data interface applications in C# - all on top of SQL Server 2005. This environment works/worked fine until I started dealing with a lot more data. I now have 2 databases, each about 30 gig in size (with most of the data in a a couple of tables in each database). When I am importing data or running the website or doing intensive queries or doing sql updates that change millions of rows, I am understandably hitting performance problems. I have had to throttle SQL Server 2005 to only use 400mb, as I have so many other development applications that are open...... Everything is slowing to a halt, and I dont think SQL Server is very happy. So, I am looking to build or buy a cheap machine to run SQL Server 2005, and take the load off my current PC. However I only have a budget of about Ўк300/$560 - maybe a bit more at a push (I could possibly upgrade in the future). I could do something like below, if I am going to buy a machine Acer T180 Sempron 3200 512mb (i would upgrade to 2GB) 80gb hard disk Or I could build my own If I do, what should I skimp on and where should I invest more. I.e. should I get the cheapest processor possible, and get a decent harddrive or 2 Should I raid 0 a couple of small drives Or have one drive for data and one for the transaction logs Or should I have one drive, and spend more on RAM Any help would be greatly appreciated.... Andrew Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 13
    - Silent install using VBScript Hi.. i am new to vbscripting.i need to create the vbscript for ( .netframework2.0,MDAC2.7,XML4.0,VC++2005,windowsinstaller3.0,OWC10) with silent installation via VBscript.before that i need to check the machine have my pre-requisites or not,which machine does not have my pre-requisites through script i have to install. Kindly provide any solution for this.you can reach me at senthil3.n @tcs.com. thanks, N.senthil Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 14
    - Sql Server Reporting Services 2005 returns 404 not found I am having the same problem, however it is not a new install - it had been runnning for a couple of months. As far as I know, nothing in the application or IIS settings were changed by a person. Configuration GUI has all green check marks. I stopped/started the service and I stopped/started the IIS website. I rebooted the server. I checked the file permissions in IIS - they appear to be unchanged (read access). I have looked at the eventvwr and the reporting services logs. I dont see anything out of the ordinary. I do, however, see an entry 4 days ago that indicates "Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database." I do not see another entry after that one. I verified that home.aspx is the default document. I also tried typing it into the url, without success. I also added a test page and it loads fine. What are the next places to check Is there a log or some other way of confirming that if IIS settings and/or SSRS settings were changed, what and who Thanks! - Glenda Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 15
    - exception handling - best practices hi all, not sure this is the correct section to post this, but couldn't find a more appropriate one. we are debating on sql exception handling usage and we could'nt have an agreement on whether we should trap constraint exceptions with a try-catch block or check them before executing insert/update/delete statements. one of my collegues insist that constraint exceptions should be considered in terms of error handling rather than exception handling and should not be handled by sql exception handling mechanism. He suggests to use "if exists" before every insert/update/delete as a constraint check inside a try block, so that only the records which are changed just after the if exists statement had executed may cause exception. Briefly, it boils down to this: begin try insert into table_with_some_constraint_that_is_about_to_be_violated t values a,b,c end try begin catch IF (error_number() == 2627) return 1000 -- our inproc. return code for duplicate. ELSE RAISERROR('error_message_here', 11,1) end catch OR begin try IF (NOT EXISTS( SELECT * FROM table_with_some_constraint_that_is_about_to_be_violated t WHERE x=a AND y=b AND z=c)) insert into table_with_some_constraint_that_is_about_to_be_violated t values a,b,c ELSE return 1000 end try begin catch IF (error_number() == 2627) return 1000 -- our inproc. return code for duplicate. ELSE RAISERROR('error_message_here', 11,1) end catch thanks in advance Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 16
    - Scheduling an automatic backup another option may be, go for License edition like Standard /Workgroup which can fullfill all the requirement you mentioned... SQL Server express is not supporting all the features available in Licensed Editions, so you may go for license edition of sql server 2005. Madhu Tag: Using Integrated Security from .Net Web to access SQL Server 2005
- Next
  - 1
    - Accessing the underlying sql query from the rdl file Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group). Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd). I can then manually drill down and retrieve the SematicQuery xml from the commandText field. The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of. Is this possible Thanks, Arjay P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 2
    - IBM DB2 as Data Source Thank you Jan DHondt, Could you please give me the URL for this client software Thanks again. Regards, B@ns Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 3
    - Reporting Services 2005 Subreport issue... I think you all are thinking of this problem in a programmatic sort of way in that you pass parameters from a parent report to a subreport and then return values from the subreport to the parent report. A subreport is merely a report. You can pass parameters from a subreport to other reports (ie. the parent report). See if you can pass the value of the field in your subreport back to the parent report and simply output the value. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 4
    - SSIS Data Conversion: Convert a String (like yyyymmdd) into Datatype dt_DBDATE or dt_DBDATE Hi All, I need a solution for the following: I have a field with datatype string, length 8, in the form yyyymmdd (f.e. 20070604). Now I need to transfer this field into a field with datatype dt_DBDATE or DT_DBDATE. I tried to perform this with a derrived column and type cast (DT_DATE) or (DT_DBDATE), but this does not work. Any hint for me!!! Thanks in Advance ulrike Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 5
    - SQL Compilation and execution plan Hi all, IЎЇm having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer: QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2). Is SQL server working the way expected or am I missing something Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 6
    - SQL Reporting Services - Page X of XX counts in header Doh! I am sorry, you are absolutly correct. The subreport control renders the report without headers/footers and even strips the pagination. I have not been able to find a work around, even using custom code. It appears the main hangup is that the page is being rendered as a stream and there is no mechanism for passing information backwards in that stream to fill in placeholders. It appears to be a single pass operation. The only way I see to get the functionality you require is to break your report into multiple smaller reports. Is there a reason why this will not work If it is the problem of printing all of the individual reports, how about automating the print process Here are a couple of articles on automating the print process: Article showing how to print directly from SRS. Source code in C#. http://blogs.msdn.com/bryanke/articles/71491.aspx Codeproject article showing how to print using a temporary PDF file. Source code is in VB. http://www.codeproject.com/sqlrs/RSPrintLandscape.asp Another less than optimal solution would be to set your report up to display a fixed number of rows per page and calculating the number of pages per group yourself using the Count function with group level scope as discussed in the comments of this article by Brian Welcker: If we know how many rows are on a page we can calculate the page number and force a page break! For example you group by the customer ID and add a second grouping on the row number. Group 1 Expression =Fields!CustomerID.Value Group 2 Expression =(RowNumber("table1_Group1") - 1) \ 10 + 1 On the group properties set Ў°Page break at endЎ± to True This gives us 10 records on a page. If we need to display the page number and total number of pages for this group we can use this expression in the group 2 header. = Ў°Page Ў° + (RowNumber("table1_Group1") - 1) \ 10 + 1 & " of " & ((Count( Fields!CustomerID.Value, "table1_Group1" )-1) \ 10 + 1) http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 7
    - Export to Excel Hello, Here's what I would do... Turn 'Snap to grid' and 'Draw grid' options on. Set the position of the first textbox to have the same left position as one of the columns dividers in the table, then increase/decrease the width of the textbox so that the right edge falls on the same Y coordinate as another one of your column dividers. Do the same for your second textbox and your image. Jarret Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 8
    - ?? 933097 Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available I've read about this hotfix at http://www.kbalertz.com/Feedback_933097.aspx but I can't find anything on microsoft.com about it. It sounds like a worthwhile hotfix, but I'm wondering about the absence of an announcement on microsoft.com. Anyone else know about this hotfix Thanks! Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 9
    - Layout question We have a report that is printed landscape and contains a main table with values. Inside this table we also want to create a "sub-report" table based on two other criteria (charges and credits), but this data is still grouped with the main table. Here is a simple schematic of the report XXXXXXX XXXXXXXX XXXXXXXXXX XXXXXXXXX Charges XXXXXXXXX XXXXXXXXX Credits XXXXXXXXX XXXXXXXXX XXXXXXX We have placed a rectangle in the last column of the main table to accomodate two other "sub-tables": one for Charges and the other for Credits. We need to keep the heighth of each row in the main table the same (without extra white space) so we cannot move the second table for Credits below the table for charges. I hope this makes sense and anybody with any suggestions, it is appreciated. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 10
    - Flat File Data Source with variable number of delimited columns I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file). Is it possible to use a single flat file data source that will work with all of the different width files Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 11
    - Stuck on a Inner Query Please help, I want to do a select query selecting the ID (from table 1) for where the person is under 18 and if there has been an episode (table 2) done on the person in the last 12 months (ie. Start date). The problem IЎЇm having is that there may be more than one episode that has been done. I think I need a query something like this Select t1.id from table1 t1 Where Exists(select * from table2 t2 Where t2.start_date> dateadd(m,-12,getdate()) And T1.dob>getdate() Table 1 People DOB ID 100 23/2/1980 101 12/12/1968 102 1/1/2000 103 12/12/2003 Table 2 Episode People Start_Date 45 100 30/01/2003 46 100 12/01/2007 47 101 01/01/2002 48 103 19/2/2007 Result People ID 100 103 Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 12
    - help with WHERE clause I have a situation where I want to be able to match an exact string value and if that is unsuccessful then match all strings like the search parameter. The scenario I'm working on right now is searching for users by username. An example for simplicities sake is if I have the user "admin" and "administrator" in a database, if I search for "admin", I only want that single user to be returned, however if I searched for "adm", I want both users to be returned. Here's the approach I've taken so far: SELECT * FROM Users WHERE ((@Username IS NULL) OR ((Username = @Username) OR (Username LIKE N'%' + @Username + N'%'))) I expected that if (Username = @Username) was found, a single row would be returned and the rest of the clause would be ignored. However, it seems as though the LIKE is still being executed because if I search for "admin", I still get two rows returned even though I only want the single row with the exact username. What do I need to do to achieve the results I'm looking for Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 13
    - SQL in OLE DB Hey all! Okay, this may not be the right forum, but I *think* it's an SSIS Question. If it belongs elsewhere, point the way and I'll repost it correctly. I'm importing a flat file, and then I want to check to see if a column of the input matches a column of a reference table. INSERT INTO BATCH CASE WHEN IN-REC-TYPE == BATCH.REC-TYPE THEN (REC-TYPE, CYMD) VALUES (IN-REC-TYPE, IN-CYMD) ELSE BLAHBLAHBLAH Okay, so it doesn't like that bit in red up there. Is there some other fast way to do this In other words, how do i refer to my input columns within an OLE DB Command block Thanks! Jim Work Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 14
    - Unused Connection Managers Chris, one way to handle this is to create 6 configuration files with 1 connection in each. Each package uses only the configuration files that apply. This prevents the annoying warnings from BIDS and allows us to use configurations in a modular way. Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 15
    - Please help me SSIS... Sorry about that - I need to beg your patience, since I missed the "text files as destinations" point when I was reading your original post. Do the source tables (and from there, the destination text files) share a common schema, or do they have different columns and data types Tag: Using Integrated Security from .Net Web to access SQL Server 2005
  - 16
    - How to debug database engine stored procedures on SQL Server 2005? 1). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues. I can't find this functionality in the SQL Server Management studio. 2). By the way I also can't find where went the output of my print statement. 3). Does server cursor functionality is still working in 2k5 Tag: Using Integrated Security from .Net Web to access SQL Server 2005

jkw

If I am posting to the wrong forum, please point me in the right direction.

We have upgraded to SQL Server 2005 and Window 2003 from SQL Server 2000 and Windows 2000, and have been having all kinds of problems with security of our web applications. We have been forced to put the system account of the web server as a user in the database in order for the web applications to work. We have lost the ability to control security at the user/role level. Is this the way security is going to work in Windows 2003/SQL Server 2005 How do I use integrated security so that I can secure web pages and database objects

Thanks

Re: Using Integrated Security from .Net Web to access SQL Server 2005

Jens K. Suessmeyer

Well, that depends. Using SQL Server 2005 and Windows Server on different boxes, you will have to setup delegation and a server principal name within your AD. having them on the same box and in additin if the above option applies, you will have to set the web.config security part

<identity impersonate="true" />

Remember that this only works if you set the virtual directory for Windows Authentication. using Anonymous access will use the specified account to logon againstg SQL Server.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

Re: Using Integrated Security from .Net Web to access SQL Server 2005

jkw

We do have the database and web on different boxes. Thanks for the assist

Re: Using Integrated Security from .Net Web to access SQL Server 2005

Jens K. Suessmeyer

Well, then you will have to set the SPN (service principal name) for security delegation.

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

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---