Hi,
I have one Script Component and two OLE DB Destinations in a Data Flow Task. They all insert some records to a table. I set the TransactionOption for the Data Flow Task to be Required. When one of the OLE DB Destinations failed, the other OLE DB Destination rolled back, but the Script Component didn't. Is there a way to let the Script Component step roll back its transaction as well when the other steps fail?
Thanks,
Jia
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
Hi, I'm relatively new to the admin side of websites and DBs. In the past, with hosted environments I've been used to just using SQL logins to connect to the DB from a web app, but for something I'm delevoping at the moment I have control over the DB and web server and was wondering if using a trusted connection would be better. After reading around it seems that Microsoft warns against using mixed mode authentication because of clear text passwords, the DB I'm working again currently isn't in mixed mode. So far I have set up the application to run in its own application pool under a limited user account that also has limited access to the DB. Though like this every new application that needs access to a different DB would require a different pool. What I'm asking essentially is what's the best way of going about things; set up a new pool and user for each app that requires it, using a trusted connection or turn on mixed mode authentication on on the DB. Or a mixture of the two?
View 5 Replies View Relatedif one connects to SQL server (2005) with integrated security, does sql server connect to ADS in order to verify the credentials or does windows handle the authentification mechanism ?
thanks
Does anyone know how to impersonate a user and then use integrated security with SQL server? Every place I've looked so far only shows how to use integrated security through IIS. For some reason, everytime I impersonate a user account, SQL server identifies me as "NT AUTHORITYANONYMOUS LOGON" Is this by design? or am I doing something wrong?
View 4 Replies View RelatedHi,
I am writing project in asp .net with sql server 2005. But I need support him by some applications like deamon in linux systems.(but it will be in windows) So i write it in java. I download jdbc driver and install it. I wrote simple project:
package MainW;
//import javax.mail.*;
//import javax.mail.internet.*;
//import com.sun.mail.smtp.*;
//import javax.swing.*;
//import java.awt.*;
//import java.awt.event.*;
//import java.io.*;
import java.sql.*;
//import java.text.DateFormat;
//import java.text.SimpleDateFormat;
//import java.util.*;
//import java.util.Date;
class Main
{
public static void main(String[] args)
{
String connectionUrl = "jdbc:" + "sqlserver://localhost;databaseName=ASPNETDB.MDF;IntegratedSecurity=True";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
String SQL = "SELECT * FROM aspnet_Users";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
// Iterate through the data in the result set and display it.
while (rs.next())
{
System.out.println(rs.getString(4) + " " + rs.getString(6));
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
But i have error:
2007-08-20 15:20:59 com.microsoft.sqlserver.jdbc.AuthenticationJNI SNISecGenClientContext
WARNING: Initialize context failed
2007-08-20 15:20:59 com.microsoft.sqlserver.jdbc.SQLServerConnection SSPIData
WARNING: ConnectionID:1 TransactionID:0x0000000000000000 Authentication failed code-2146893052
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.SSPIData(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at MainW.Main.main(Main.java:29)
I am googling about this error second day but i cannot find solve.
Please help.
If one connects to sql server (2005) with integrated security, does sql server connects to ADS in order to verify credentials or does windows handle the authentification mechanism ?
Thanks.
Hello,
I'm new to ASP, but developping in Sql for years.
What we would like to have is that the user is accessing the database over it's own Windows Logon. Our triggers log quite some changes and are using UserName() for this. I've treid to force the IIS to accept Windows Integration only, the SqlDataSource users a connection that has Integrated Security = True. But when connection to the site i'm gatting error that there is no trusted connection for the user . (dot) ...
I suppose i'm missing something but could you give me a hint where to start looking..... THX
I'm building a reporting server that will grab data off of a an old HP3000, all of that is being done in a background task (non asp.net) and uploaded to an sql server.
I'm using a web interface to allow people within the company to view the data. This will be in a single domain, behind a firewall, and will not be accessable from outside of the company.
In IIS, I have anonymous login unchecked and only Integrated Windows Auth checked. The pertinate parts of my web.config look like this:
<authentication mode="Windows" />
<identity impersonate="true" />
<authorization>
<allow users="*" />
<deny users="?" />
</authorization>
My connection string looks like this:
public static string SqlServerConnect = "Integrated Security=SSPI;Initial Catalog=[name of database];Data Source=[sqlserver IP]";
The website (for now) is on my local development computer. When I run the program everything works fine, when I attempt to view the web page on another computer, I get the following error
Server Error in '/reports' Application.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I'm at a loss as to how to fix this. I dont really want each user to have to enter any log in information, frankly, the permissions dont even matter in this case. My main problem is with the sql connection and trying to get adequate permissions to run queries.
Any help would be apprieciated
We have a a DTS package set up to run against another SQL Server. Using an integrated login is there a way to map an NT Authenticated users is
the sql server login id mapping to this attached server. The DB we are going against only uses NT authentication to attach to.
Hi All,
Can we implement a table level (object) security on the groups of the windows NT domain using the integrated windows NT security so that one group has
the permission over one table to update ,select ,delete the information and other group has permission has for some other table.
Thanks
Ajay
We are researching a SSRS and SharePoint integrated mode install and I have a question about report development. When SSRS is installed as integrated mode, there is no report sever to publish to and the reports are published to document libraries. What do you do when developing reports in Visual Studio? How do I publish the new reports?
I saw a white paper what seemed to suggest that you have to do report development in a native reporting services environment and re-publish to SharePoint. Is that true?
Here's a link to a blog that has the information:
http://blogs.msdn.com/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx
Thanks for the Help!
-Scott
We have 64 bit MOSS with integrated SRS. This works quite well. You lose data-driven subscriptions until the release of SQL 2008. We want to build a custom subscription/workflow solution that renders reports and pops them into SharePoint lists. What we want to do is render thousands of reports into SharePoint document libraries and use workflow rules to route them to file shares, emails, approvals, etc.
This can be done manaully by right clicking the RDL in a report library and selecting Manage Subscriptions. This brings up a web page listing existing subscriptions (_layouts/ReportServer/ManageSubscriptions.aspx). This page does not write to a SharePoint list, but a set of tables in the ReportServerIntegrated database. I can see the tables and stored procedures that do the work. I cannot find a programming interface to create the subscriptions in code. I guess I can just call these stored procedures.
Has anyone played with this?
Hi,
is possible to integrate SQL Server Express with SharePoint or it's possibile to integrate only the SQL Server 2005 version?
Thank's
I've had VS2005 installed for quite some time but I just purchansed SQL Server 2005 Developer and installed it. How do I accomplish integeration with SQL Server 2005 to VS2005? I had C# express at one point and SQL Express with the full tool install. That provided me with a designer for SQL Server (which was simply VS2005) and a seperate development environment under C# express. How do I integerate them so all the functionality resides within VS2005?
Any help you can provide will be much appreciated.
Thanks,
Dane R. Vinson
or do i need to setup SQL security users and logins.?
View 1 Replies View RelatedHi!
I wonder where in Reporting Services Configuration
i can change the mode to Share Point Integrated?
Do i need to create a new database,
or can i change the mode for the one i use?
//C
In my architecture I have a Domain Controller with Active Directory (DOMAIN_A), IIS 6.0 (WEBSERVER) and SqlServer 2000
(SQLDBSERVER).
The WEBSERVER has a
.NET application with windows authentication. The .NET application interacts with the
database server. I want to use Integrated Security to pass in
the users login credentials to the database to run any database calls so that I can
audit who is making what calls on the database.
The connection string
I am using for this is as follows:
string connStr =
"Server=SQLDBSERVER;Database=xxx;integrated
security=SSPI";
The problem arises
after I login to the web application (I use the user DOMAIN_ASomeUser where
SomeUser is a user who has permissions set up to make all of the database
calls). After logging in however, when I go to a page that makes a database
call I get the following error:
System.Data.SqlClient.SqlException: Login failed for user
'DOMAIN_AWEBSERVER$'.
It seems that for some
reason, .NET is not passing the login name SomeUser, but instead is passing
SERVERNAME$. Would anyone pls have any ideas how I can implement this. Any guide or references would be much appreciated.
Thanks in advance.
We are using a VB application with a "dsnless" connection. It is not able to connect to the SQL box using integrated WinNT authentication. Receive the below error message:
Run-time error '-2147217843 (80040e4d)':
[Microsoft][ODBC SQL Server Driver][SQL Server] Login Failed
We can connect to our test SQL box with using the same application with no problem. The only difference we can see is the clustering on the "real" box.
We are using the following program string to connect to both boxes with the exception of the server we connect to:
"driver={SQL Server};server=server;uid=;pwd=;database=pubs"
Any info. would be greatly appreciated!
Thanks :)
We've encountered a problem on one of our SQL servers running integrated security where MS Security Manager errors out with "An error occured executing sp_addlogin using Domain_nameusername - " is not a valid name since itbegins with an invalid character." We think it is because the domain has the underscore character in it name. Can anyone confirm or point to other possible configuration issues?
View 1 Replies View RelatedIdeally, I'd like to move away from using SQL-based logins for our internal applications and take advantage of integrated security instead.
Defining AD groups and their permissions in SQL is simple and getting the application to work with that is not an issue.
Where I'm having difficulty, though, is in isolating the accessibility in integrated security. Because the SQL-based login was isolated from the windows user, they could only get access to the sql server via our app -- their normal windows accounts had no access.
If we switch to use only windows authentication, the user would be able connect fine from our application and have rights to various tables. The issue is that they could also connect via Enterprise Manager, Excel, or any other tool. Is there any way to limit the exposure so that we can take use of AD for our access but further limit to allow connections based upon the application? I realize that this could be impersonated, but it's still better than nothing...
--Kevin Fairchild
Hi All ,I am hoping some can help or point me in the right direction. We haverecenlty installed SQL 2005 and are using integrated services using oledb connections. We are connecting to an Informix database.My questions is there a way I can pass through multiple sql statementswhen I create a OLE DB source to the Informix database.The reason I am doing this is we need to tell the Informix DB that wewant to use "set isolation dirty read" as well as use the Parallel Dataquery part of informix.Please bear with me as I am just starting to use SQL 2005. I have notfound a way to do this. Can anyone suggest something please.If you need more Information please also let me know.ThanksTraveller
View 2 Replies View RelatedHI,
I've got the folloing scenario:
Client app is a windows forms app on client machine. Web Service on a dev machine (Windows 2003 standard), SQL Server 2000 on a seperate machine. The user logs on to the using their windows login. I'd like to pass these credentials to the web service, have the web service impersonate the user and access the SQL Server with these credentials... not a problem I thought.
Setup windows integrated login on IIS for the web service, impersonate="true" in the web.config, Mixed mode on the SQL server. Passed the network credentials through to the Web Service, and this works correctly, however when the web service (impersonating the user) tries to access the SQL Server, I get the message about access denied, user is null, blah, blah. (if remote access the Web Service box, and call the web service locally, all works well, and voila, until the thread recycles, the client machine access also works... same user/pass)..
I've seen various posts about this but nothing seems to solve the problem. Am I right in thinking that this method (Windows integrated login) of passing credentials through to the SQL Server is the best method? If so... er, shouldn't it work? What am I doing wrong here? and if it doesn't work, what other method would work?
I've just about given up on this and thinking of passing the SQL Server Username/Password across the network (shudder), encrypted yes, but not ideal!
Anyone got any idea?
Regards
Kevin
Hello,
After searching the SQL reporting services forum for a while, I decided to lay this problem before you.
I am faced with the following problem:
I want to generate a report containing video data, this videodata is streamed into a SQL 2005 database.
The main question I have is whether it is at all possible to show moving images in an report? (for instance with a CRI?)
Or should I look in another direction (I initially thought of an external link to HTML file with an MediaPlayer Object and having the file stored on a harddisk for instance?)
Any help on this subject would be greatly appreciated!
kind regards,
Michiel
Hi ,
I am trying to deploy reports through visual studio. In the Target server URL, specified the url of the report server as http://<localhost>:<portnumber>/reportserver/Shared Documents/. But
When i deploy it asks for username and password. when i provide them, again login screen appears. Finally deployment fails when i press cancel.
Did anybody face this kind of problem ?
Thanks & Regards,
Kiran Kumar
Hello,
We're having a bit of a problem getting Integrated Security to work with a .Net 2.0 application and SQL 2005. While we're tweaking permissions on the SQL-side, we came across an account "Application Login" and wondered what its role is. First, our problem:
Currently, the users in the AD group get a connection error. This group is defined as follows at the instance level:
role: public
user mapping: to the database without any default schema
securables: none
status: grant and enabled
At the database security level:
general: none
securables: execute on all (100+) stored procedures
And we gave them "Execute" on the database itself.
A little background: we had detached and copied this database from one server to another. So we suspect that the Application Login may have been modified/corrupted, even though it appears to be identical between the original and the copied databases. So we redefined it on the copied DB to match the original.
Another group, which is defined as dbo on the database, has no problem at all connecting and running the application.
The Application Login has Execute permissions on all stored procedures and Delete, Insert, Select, Update, and View Definition on the ChangeLog table. It also has db_DataReader, db_DataWriter, and db_ddlAdmin roles associated with it.
Is there another SQL login required for initial connection to the database even though Integrated Security=SSPI is used in the connection string?
Does anyone see where we may be missing a security setting for the non-dbo user group to connect to the database?
Thanks very much for any suggestions, ideas ....
Cheers,
Tess
Hi,
I have an installation with Reporting Services in integrated mode with WSS 3.0, with Reporting Services Add-in for SharePoint.
I have a document library in witch I added a data source to an SSAS database and a Report model on top of this. When I click Add a Report with Report Builder, the report builder opens, but it fails to connect to the model.
There is a dialog window "Select Site or Server "asking for a URL. I put in there http://localhost, but is not working. I am not able to connect to the report model.
I will appreciate any help on what could be the reason or how can I investigate this.
Thanks,
Dan
P.S.
I did the same scenario on the development environment it works from start, without any problems. But now on production €¦.
Hi,
My OS is Windows Vista and I'm running SQL Server 2005 with SP2 and I have installed JDBC driver sqljdbc1.2 on my laptop.
I tried to connect it to the database Employee on Database Enginee Server
Here is the code:
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
public class MSsqlConnect{
public static void main(String[] args) {
System.out.println("Microsoft SQL Server 2005 Connect Example.");
Connection conn = null;
String url ="jdbcqlserver://localhost:1433;integratedSecurity=true";
String dbName = "Employee";
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String userName = "Gurumurthy";
String password = "pa55word";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url);
System.out.println("Connected to the database");
conn.close();
System.out.println("Disconnected from database");
} catch (Exception e) {
e.printStackTrace();
}
}
}
When i run the program i got the msg:
C:UsersGurumurthyDesktop>java MSsqlConnect
Microsoft SQL Server 2005 Connect Example.
26 Oct, 2007 10:05:09 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integra
ted authentication.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Sou
rce)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown S
ource)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at MSsqlConnect.main(MSsqlConnect.java:16)
Could anyone pls help me out to fix it.
thanks & regards,
Gurumurthy
Up until an hour ago, I was using the Sql Server Reporting Services Report Viewer webpart in Sharepoint with forms authentication, and was not having an issue. The only thing that I did (or remember doing) before things went sour was upload a new report to the document list and since then whenever I try to access a report, manage the data source on a report, change the report URL in the webpart, I get the following message:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl=%2f_vti_bin%2fReportServer%2fReportService2006.asmx">here</a>.</h2>
</body></html>
--.
I found some related posts and have tried the following, but none have helped:
Removed all reports and reuploaded only the one that was definitely working.
I changed the application pool for the report server to be the same app pool as Sharepoint (runs under domain admin account).
I changed the application pool for the report server back to it's original (which the Reporting Services Configuration prefers - was running under Network Service acct before the problem started, now running under domain admin acct)
I can browse to http://server/reportserver.
I'm running under forms authentication, which had been working.
I can access the three configs for Reporting Services under Application Management in Central Admin - I've tried changing the settings.
I can access http://server/_vti_bin/reportserver/reportservice2006.asmx as well as http://server/_vti_bin/reportserver/reportserviceauthentication.asmx (that was suggested in one post)
I've restarted IIS numerous times, and also finally rebooted the server.
I've created a new document list and set it for report content, just like the other list.
I also just noticed that even though I've set up my admin account as a site collection administrator, I now no longer see my report options under the New button in the document library (both the old and new). I don't know if this is related.
I'm sure that this is a case of something minor that I did that I forgot about. Any suggestions or other troubleshooting that I can do?
Thanks!
daw