I like to use impersonation using multiple databases and a user with no login.
I'm working with Powerbuilder 10. I can change users using the command Execute Immediate "EXECUTE AS USER = 'username'". Unfortunately, I can't execute the command 'REVERT' from Powerbuilders Execute Immediate command. The Execute Immediate command prefixes the 'REVERT' command with a exec. ie. exec REVERT.
I thought I could encapsulate the REVERT command in a procedure and run the procedure using Execute Immediate. But, I'm new to SQL Server and I'm not sure if I can.
Does anyone know how to solve this problem? Thanks.
Hi All, I am totally new to databases. I am starting from the absolute beginning. I want to learn MS-SQL and was wondering how/where to start. I need info on everything from installation and set-up on an Windows XP PC to programming in SQL. Is there a book or website that can guide me. I am fairly decent at programming and am able understand technical books. Installation and set up are my main concerns right now, since I believe that once I have a stable system to learn on, SQL should be easy. Thanks!
Hi I have double checked my code and cannot pin down why I am getting the error "There is already an open DataReader associated with this Command which must be closed first. " on the lne: Line 95: DR_IndJobPostings = oComm_IndPostings.ExecuteReader(); I have closed the DR_IndJobPostings object after every use of it as see n on line number 175
----------- Code--------------1 /// <summary> 2 /// Will generate and email job alerts based on the frequency 3 /// </summary> 4 /// <param name="frequency">WEEKLY or MONTHLY</param> 5 /// <param name="oServerIN">Instance of Server from ASPX page</param> 6 public void hk_DoAlertByFreq(string frequency, HttpServerUtility oServerIN) 7 { 8 SqlConnection oConn = new SqlConnection(ConfigurationSettings.AppSettings["CString"]); 9 oConn.Open(); 10 11 SqlCommand oComm; 12 13 emailSystems oEmail = new emailSystems(); 14 HttpServerUtility oServer = oServerIN; 15 16 bool validCall = false; 17 bool industryHasPostings = false; 18 string sEmail = ""; 19 string sEmailTemplate = ""; 20 string sVacListForEmail = ""; 21 22 int IJPost_VacId = 0; 23 int IJPost_EmpId = 0; 24 string IJPost_Req = ""; 25 string IJPost_KeyRes = ""; 26 string IJPost_VacTitle = ""; 27 string IJPost_VacJobTitle = ""; 28 string IJPost_VacUrl = ""; 29 30 int loopCounter1 = 0; 31 32 string CandEmailAddress = ""; 33 34 oComm = new SqlCommand(); 35 oComm.Connection = oConn; 36 oComm.CommandType = CommandType.Text; 37 38 SqlCommand oComm_IndPostings = new SqlCommand(); 39 oComm_IndPostings.Connection = oConn; 40 41 SqlDataReader DR_Industries; 42 SqlDataReader DR_IndJobPostings; 43 SqlDataReader DR_AlertList; 44 45 if (frequency == "WEEKLY" || frequency == "MONTHLY") 46 { 47 validCall = true; 48 } 49 50 if (validCall) 51 { 52 if (frequency == "WEEKLY") 53 { 54 sEmailTemplate = oEmail.readTextFile("/email_templates/weeklyJobAlert.txt"); 55 } 56 57 if (frequency == "MONTHLY") 58 { 59 sEmailTemplate = oEmail.readTextFile("/email_templates/monthlyJobAlert.txt"); 60 } 61 62 sSql = "" + 63 "SELECT [id],[industry] FROM S_Utils_Industries " + 64 "WHERE [active] = 1"; 65 oComm.CommandText = sSql; 66 DR_Industries = oComm.ExecuteReader(); 67 68 if (DR_Industries.HasRows) 69 { 70 // 71 // Loop through each active industry 72 // 73 while (DR_Industries.Read()) 74 { 75 industryHasPostings = false; 76 iCurrentIndustryId = (int)DR_Industries.GetSqlInt32(0); 77 sCurrentIndustryText = DR_Industries.GetSqlString(1).ToString(); 78 79 // Get all active vacancy postings for this 80 // industry 81 sSql = "SELECT [id]," + 82 "[emp_id], " + 83 "[vac_Requirements]," + 84 "[vac_KeyResp]," + 85 "[vac_VacTitle]," + 86 "[vac_VacJobTitle]," + 87 "FROM [S_Vacancies] " + 88 "WHERE [vac_VacIndustry_Id] = " + iCurrentIndustryId.ToString() + " AND " + 89 "[status] = 1 AND " + 90 "[vac_ListingStart] >= '" + gf.SqlDateTimeFormat(DateTime.Today,1) + "' AND " + 91 "[vac_ListingEnd] < '" + gf.SqlDateTimeFormat(DateTime.Today, 1) + "'"; 92 93 oComm_IndPostings.CommandText = sSql; 94 95 DR_IndJobPostings = oComm_IndPostings.ExecuteReader(); 96 97 // 98 // If there are job vacancy postings for the industries 99 // 100 if (DR_IndJobPostings.HasRows) 101 { 102 industryHasPostings = true; 103 sEmail = sEmailTemplate; 104 105 // 106 // Loop through the job postings for this industry 107 // 108 while (DR_IndJobPostings.Read()) 109 { 110 IJPost_VacId = (int)DR_IndJobPostings.GetSqlInt32(0); 111 IJPost_EmpId = (int)DR_IndJobPostings.GetSqlInt32(1); 112 IJPost_Req = DR_IndJobPostings.GetSqlString(2).ToString(); 113 IJPost_KeyRes = DR_IndJobPostings.GetSqlString(3).ToString(); 114 IJPost_VacTitle = DR_IndJobPostings.GetSqlString(4).ToString(); 115 IJPost_VacJobTitle = DR_IndJobPostings.GetSqlString(5).ToString(); 116 IJPost_VacUrl = "http://www.mann-power.net/vJDetails_FromFront.aspx?vid=" + IJPost_VacId.ToString() + "&from=myjobs"; 117 118 sVacListForEmail += IJPost_VacTitle + @" 119 120 Job title: " + IJPost_VacJobTitle + @" 121 122 Key Responsibilities 123 " + IJPost_KeyRes + @" 124 125 Requirements 126 " + IJPost_Req + @" 127 128 " + IJPost_VacUrl + @" 129 130 ============================================================ 131 132 "; 133 } 134 135 136 sEmail = sEmail.Replace("{VACANCYLIST}", sVacListForEmail); 137 138 // If there are job postings for this industry 139 // get all the people who signed up for a job alert 140 if (industryHasPostings) 141 { 142 sSql = "SELECT [S_JobAlerts].[IndustryId]," + 143 "[S_JobAlerts].[candidateEmail] " + 144 "[S_Cv_Status].[Cv_Online], " + 145 "[S_Cv_Status].[usingShortResume], " + 146 "[S_Cv_Status].[iHasHadIntro] " + 147 "FROM [S_JobAlerts] " + 148 "INNER JOIN [S_Cv_Status] ON " + 149 "[S_Cv_Status].[user_id] = [S_JobAlerts].[candidateUserId] " + 150 "WHERE ([S_JobAlerts].[frequency] = '" + frequency + "') AND " + 151 "[S_JobAlerts].[IndustryId] = " + iCurrentIndustryId.ToString() + ""; 152 153 oComm.CommandText = sSql; 154 155 DR_AlertList = oComm.ExecuteReader(); 156 157 // If there are candidates who signed up 158 // for a job alert 159 if (DR_AlertList.HasRows) 160 { 161 // 162 // Loop through each job alert for this industry 163 // 164 while (DR_AlertList.Read()) 165 { 166 CandEmailAddress = DR_AlertList.GetSqlString(1).ToString(); 167 oEmail.sendSingleMail("john.cogan@staffmann.co.za", "Mann-power Job alert", sEmail); 168 } 169 170 } 171 DR_AlertList.Close(); 172 173 } 174 } 175 DR_IndJobPostings.Close(); 176 177 } 178 179 } 180 DR_Industries.Close(); 181 182 183 oConn.Close(); 184 } // END: if (validCall) 185 186 }
I have a utility server that I am running SS2K5 SP2 w/ the latest patches.
It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.
I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.
It DID created the linked server BUT when it finished up it generated the following message:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio ------------------------------ "The test connection to the linked server failed." ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).". OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
============= ERROR TEXT END ==============
Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:
================ ERROR TEXT BEGIN ======================
TITLE: Microsoft SQL Server Management Studio ------------------------------ Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
============= ERROR TEXT END ==============
Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)
Now I have masked the real values in this post of the @provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.
I have an issue with an SSIS package I was hoping to get some commentary on.
I am taking a flat file, scrubbing it in SSIS, and exporting it into a different package.
The files are fixed width. Both Fixed Width, and Ragged Right settings are not working, I've tried both.
My current config on the flat file connection is set to use the file name as variable. The format is "Ragged Right". The Text Qualifier is "<none>". The Header row and Header rows to skip are <CR><LF> and 0. The column names in first row are not checked.
In the advanced tab, 43 columns are defined. The output and input have all been verified numerous times to conform to the file spec.
When previewing the file in preview, only one record shows up even though the file contains multiple.
What it's doing is the last column contains the CR and LF characters and then it continues putting the other rows in that column (it is ignoring the CR LF and not going to the next row)
WHen I click on "Columns" in the flat file connector, it displays the rows of information as it should. When I click back down to preview a second time, the rows are displayed as they should.
The initial time you preview, the rows are jacked up and all smashed onto the first row. WHen trying to execute the package, I get a truncation error because the last column is supposed to be 7 in length, and contains multiple data rows in it.
When trying the option "Fixed Width" it does the same thing. It ignores the CR LF and makes everything one row.
Can someone please explain what it is I'm doing wrong? Or why when I click to preview the first time it is broken, but when clicking on columns and then back to Preview it is fixed?
Not sure if this is exactly the place to post this, but here it goes anyways.
I am writing a ASP.Net/C# program and I am interacting with a MS Access database in order to derive data on user login and logout times. Basically, I am trying to create a line graph that will display the number of users over the course of a user specified timespan. Currently, I am doing this by look at the number of users that were logged on during each minute of the timespan.
My database table setup consists of a EmployeeID column (Text), Logon Date (Date/Time), and Logoff Date (Date/Time). I have also created an index on the Logon Date and Logoff Date columns.
In order to view the number of users during a minute of the timespan I use a Jet SQL query of the following format
Code Snippet SELECT DISTINCT Count (EmployeeID) AS [User Count] FROM ProgramName WHERE ([Logon Date] < #August 27, 2007 11:45:00# OR [Logon Date] Is Null) AND ([Logoff Date] > #August 27, 2007 11:45:00# OR [Logoff Date] Is Null)
The problem is that using this method I have to execute 1,440 queries for each day in the timespan. Currently this takes about 25 seconds to execute if the timespan is a full workweek (7,200 queries).
Now the question. Is it possible to create a SELECT statement that will return user counts for multiple minutes? Like maybe a SELECT statement that returns a column of counts for every minute in an hour? If it is possible, does anyone have any examples? I am hoping by lowering the number of queries my program has to execute I will also cut down the time required for the code to run.
I am pretty new to SQL, so any guidance or advice is very appreciated.
I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.
However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).
Is there any way I can do this?
My query is like this: SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]
I want a user to be able to call a stored procedure, that will call an assembly, that will logon on to another SQL Server, perform some functions (calculations), and return the results. I want the user's credantals passed, NOT the SQL Server Account. So in some research, I created this:
Public Class SomeName <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub LinkedServer() Dim cmd As SqlCommand Dim dr As SqlDataReader Dim clientId As WindowsIdentity Dim impersonatedUser As WindowsImpersonationContext
' as usual, connection strings shouldn't be hardcoded for production code Using conn As New SqlConnection( _ "Data Source=SERVER1; Initial Catalog=master; Integrated Security=SSPI") conn.Open()
cmd = New SqlCommand( _ "SOME QUERY", conn)
dr = cmd.ExecuteReader()
SqlContext.Pipe.Send(dr)
End Using End If Finally If impersonatedUser IsNot Nothing Then impersonatedUser.Undo() End If End Try
Catch ex As Exception SqlContext.Pipe.Send("Error: " & ex.Message) End Try End Sub End Class
Now the issue is that I get this message when I execute this code with the Impersonation code.
Msg 10312, Level 16, State 49, Procedure spr_SQLServerAccess, Line 0
.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
When I exclude the impersonation code, everything works, BUT executes under the SQL Server Account.
I have used this code to create the Assembly and Stored Procedure:
-- Register the assembly
CREATE ASSEMBLY SQLServerAccess
FROM 'c:linkedserver.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS
GO
-- Register the stored-procedure
CREATE PROCEDURE spr_SQLServerAccess
AS
EXTERNAL NAME SQLServerAccess.SomeName.LinkedServer
Any idea's on the error message that is being thrown by SQL WITH the Impersonation code?
What's the correct way to set up impersonation & SQLExpress Here's the error I'm getting:Cannot open database "aspnetdb" requested by the login. The login failed. Login failed for user '***ASPDATA'. SQL Express in installed on C: aspnetdb was set up from aspnet_regsql.exe, on IIS manager - asp.net tab - edit configuration this string is there: data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true The aspnetdb is located in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataThe ASP.Net web is on D: webconfig file has: <add name="LocalSQLServer" connectionString="Server=.SQLEXPRESS;Database=aspnetdb;Trusted_Connection=Yes;" /> <authentication mode="Windows" /> <identity impersonate="true" userName=aspdata@xxx.org password="xxx" /> Should I take a copy of aspnetdb and put it in the web app_data folder?Jess
Hello all- Before I go any further, I have followed http://msdn.microsoft.com/en-us/library/ms188304.aspx as best possible. I am attempting to send mail through a DML trigger. We'll call the database 'DB', and it is owned by a domain account named 'DOMAINAcct'. The trigger simply blocks any CUD operations on a table which we'll call 'Tbl', and sends an email. Hence, it looks something like...
CREATE TRIGGER [dbo].[TR_Tbl_BlockChanges] ON [dbo].[Tbl] WITH EXECUTE AS OWNER INSTEAD OF INSERT,DELETE,UPDATE AS EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'AcctMail', @recipients = 'foo@bar.com', @subject = N'CUD operations not allowed on Tbl', @body = N'Blocked'
AcctMail is a valid profile and operates correctly. I have created the DOMAINAcct user in msdb, given it the AUTHENTICATE permission, and added it to the DatabaseMailUserRole. When the trigger fires, according to the article, the security context should switch to dbo (DOMAINAcct), then be successful when attempting to execute the msdb sproc. Instead I get the usual: Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
I am installing an application that is a WCF service host running as a windows service under the Network Service account. As part of its configuration I am creating a connectionstring in a config file that will allow the WCF services to access SQL Server. I would like this access to be done using windows authentication not sql server authentication.
So since the windows service is running Logged in under the Network Service account using the above connection string would try to connect to sql server using Network service account. Instead I would like to impersonate another domain account which has has a sql server login and is a user in the database.
Is there a way to configure the connection string to use integrated security but to impersonate another domain user?
In the following scenario, I am getting the message 'Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection'.
I am running a Windows Server 2003 with development environment and Sql Server Management Studio in a workgroup on a virtual PC.
My SQL Server 2000 is running on a domain server.
On the virtual Pc I have setup my user login and password to be the same as my domain login and password. Why is the Management Studio not using impersonation and allowing me to connect to the SQL server on the domain?
This is driving me nuts, below is the C# for the proc as well as the runtime error upon calling EXEC on it. Any help would be appreciated. Using UNSAFE Permission Set.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security;
using System.Security.Principal;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void uspExternalConnection()
{
WindowsIdentity newIdentity = null;
WindowsImpersonationContext newContext = null;
try
{
//impersonate the caller
newIdentity = SqlContext.WindowsIdentity;
newContext = newIdentity.Impersonate();
if(newContext != null)
{
using (SqlConnection oConn =
new SqlConnection("Server=.\sqlexpress;" +
"Integrated Security=true;"))
{
SqlCommand oCmd =
new SqlCommand("SELECT * FROM AdventureWorks.HumanResources.Employee", oConn);
throw new Exception("user impersonation has failed");
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
finally
{
if (newContext != null)
{
newContext.Undo();
}
}
}
};
Msg 6522, Level 16, State 1, Procedure uspExternalConnection, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'uspExternalConnection':
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users? thanks,
I am testing RS2008 CTP6. When I view a Adventureworks sample report (e.g. company sales) I get this errormessage when I use the option "Impersonate the authenticated user after a connection has been made to the data source" :
Cannot create a connection to data source 'AdventureWorks'.
Must declare the scalar variable "@ImpersonatedUser".
All other connection options without impersonate works fine. Any idea what can cause this problem?
I want to Access External resources inside the CLR Code... But I am getting Security Exception
I have marked Assembly with External Access... here is the way I am doing..
I read articles and MSDN .. everywhere is written to use impersonation like
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext c = id.Impersonate();
//perform operations with external resources and then undo
c.Undo();
}
In above case .. I tried both Windows Authentications and SQL Authentications ...
In case of Windows.. I am have a domain login to logon to my pc, while sql server is at another machine and Active directory is at different machine .. when connect to Database .. it says cannot find user Domainnameuser
and the SqlContext.WindowsIdentity is always null or it has exception User.Toked thew Security exception.
After that .. I tried to user custome Identity .. using IIdentity =GenericIdentity("UserName","Windows");
But there is now difference .. still same exception .. as given below..
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProcedure()
{
Process[] p = Process.GetProcessesByName("YPager"); //Yahoo messanger exe .. a process
p[0].kill();
}
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProcedure': System.Security.SecurityException: Request failed.
I have a user who is trying to run a job (call an Stored Procedure) which connects to a Linked Server. He can run it OK using EXEC SP_Name but when he runs from the SQL Jobs it gives him the error: Linked servers cannot be used under impersonation without a mapping for the impersonated login.[SQLSTATE 42000] (Error 7437). The step failed.The Linked Server was setup using another account. Would this be fixed if I add the new user to the Security section of Linked Server without breaking the current configuration?
As a bit of background first, I'm trying to write a CLR stored proc that will start/stop a Windows Service using the ServiceController class.
The problem I'm having is that the stored proc gets run as NT AUTHORITYNETWORK SERVICE - ie the user the SQLServer Windows Service runs as. This user doesn't have adequate permissions to start/stop a Windows Service (the user only has permission to view the service's status).
The Window's user who is connected to the db - executing the stored proc, does however have adequate permission to start/stop the Windows Service. I'd like to have someway of running the code in the stored proc as if it were this user. If someone could point me in the right direction I'd appreciate it.
I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:
\a0amsimmsworkseaborg argets11as2981
This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)
CWD is: C:WINDOWSsystem32
User is: amsimms
Initial is: dbserver
Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?
When publishing to a file share using Reporting Services (no service pack 2 yet) the following error occurs:
Failure writing file NewFile.mhtml : An impersonation error occurred using the security context of the current user.
I have tried publishing to both Windows XP and Server 2000. The Reporting Services box is Server 2003. Publishing account is Local Administrator on both Reporting Services and target boxes. Logon Locally has been granted on both Reporting Services and target boxes.
Hello All, Login failed for user '(null)'. I know this issue is all over the forum, however i have not found any posts that help me resolve the issue. Situation: I have an ASP.NET 2.0 application hosted currently on XP pro(will be moving to 2003 Server) which connects to a SQL 2000 database that resides on a different server. I have taken the following step to implement my security. Given my account permissions to the database Put the following in my web.config <add name="MyName" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI" providerName="System.Data.SqlClient" /> <authentication mode="Windows" /> <identity impersonate ="true" /> I have set IIS to use integrated authentication and removed anonymous. The application works when run from the web server but not when run from a remote machine. Thank you for any asistance, George
We have a current situation where analysts will be modeling a variety of problems, all stemming from the same source data (stored in a SQL-Server 2005 relational database).
Analysts that work on the same problem will only have access to:
- A sandbox relational database (which contains views into the same source database). The analyst is db_owner of the sandbox database, so she/he can create data transformations required, etc. The sandbox database contains views to the source database, but the analyst only has read-access to the specific data elements needed from the source DB. So, they are very restricted w.r.t. the source database, but are db_owners of their sandbox relational databases. Note that the analyst will connect to the database via Windows Authentication.
- An Analysis Services sandbox database to use for their modeling, etc. In this AS sandbox db, we've created a role called "Administrator" and checked the permissions: Full control (Administrator), Process database, and Read definition. The analyst's windows account is the "user" associated with this role.
Also, in this situation, the SQL Server 2005 Relational Engine and Analysis Services are running on a single machine. The goal of this security model is to provide analysts with the ability to work in their "workspaces" (both SQL and AS), but not to see other analysts work, etc.
I'm running into a problem when trying to build models using this security model by doing the following: - Running Visual Studio - Selecting File -> Open -> Analysis Services Database and choosing the AS DB that I have access to (this is the only one that appears in the drop-down, after specifying the AS server). - I've created a data source pointing to the relational sandbox DB. - I've created a data source view choosing the table/view needed for the case table. - I created a mining structure with a decision tree model
When I process the mining structure, I'm getting the following errors:
- If the data source Impersonation is "Default" -- the error is "The datasource, '<DS name>', contains an ImpersonationMode that is not supported for processing operations."
- If the data source Impersonation is "Use the credentials of the current user" -- the error is the same as "Default" above -- "The datasource, '<DS name>', contains an ImpersonationMode that is not supported for processing operations."
- If I change the data source Impersonation to "Use the service account" and select "OK" in the "Data Source Designer" window, and error comes up with message: "The ImpersonationInfo for '<DS name>' contains an ImpersonationMode that can only be used by a server administrator.
Any suggestions or pointers to help implement this security model to provide analysts with AS and SQL Relational resources for their modeling?
