Credential Does Not Take Effect
Mar 13, 2008
I have a sqlserve, which service account is 'local system', running in machine A.
A credential ,which associated a windows user U1 in machine A, mapped to a sqlserver login Login1.
A file named 1.txt that only can be accessed by U1 in machine A.
A CLR procedure P1 that would read the 1.txt file.
I encounter a error when i access the 1.txt file through P1 as Login1:
A .NET Framework error occurred during execution of user-defined routine or aggregate "HelloWorld":
System.UnauthorizedAccessException: Access to the path 'E:1.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)
at System.IO.StreamReader..ctor(String path)
at StoredProcedures.StoredProcedure1(SqlString fileName)
.
I think it need a credential when a sqlserver login access some resource outside the sqlserver, so i add a credential and mapped to the login
any suggestions would be appreciated.
And please correct me if i have any inaccurate concept.
View 9 Replies
ADVERTISEMENT
Oct 22, 2007
I know the basic concept of credential from aritcle Credentials ,but there are some concept block me also.
1 what is outside resource
2 How to access outside resource if i create a credential.
3 is xp_cmdshell command affected If use sp_xp_cmdshell_proxy_account to creates a proxy credential for xp_cmdshell .
thanks
View 1 Replies
View Related
Apr 3, 2007
We upgraded from SQL Server 2000 to 2005 recently. I'm attempting to create a credential for the first time and I keep getting the error shown below. Can anyone tell me what to do to resolve it? Thank You
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Credential 'schjob'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Credential&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error occurred during decryption. (Microsoft SQL Server, Error: 15466)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15466&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
rm
View 2 Replies
View Related
Feb 14, 2008
There is one thing that€™s confusing me in creating a proxy account.
I am trying to get an SSIS package configured as a SQL Server job and execute it from a non-sysadmin login. But when I execute it gives the error message:
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
I know that we have to create a proxy account for this to happen and creating of proxy account prompts me to choose a credential, and that is where I do not understand the logic. From MS website I can find the following, but it is confusing to me
This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
ref: http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760
I tried reading all the related articles, but still the process of creating the credential is confusing to me, can someone throw some light on the logic of proxy/credential here?
Thanks
Satya
View 23 Replies
View Related
Feb 6, 2007
I have a report using an XML data source. The data source is calling a web service method which returns the XML file. The security setting is "Use Windows Authentication (Integrated Security)" in the report. The web service and report are both deployed to ServerA.
The web service is pulling data from a file share. Only certain users have access to this share and they are identified in the <authorization> element of the web.config file in the web service.
If I open Internet Explorer on ServerA and go to //localhost/Reports, I can navigate to the report, run the report and everything works fine.
If I open Internet Explorer on ClientZ and go to //ServerA/Reports, I can navigate to the report. However, when I run the report I get a "Failed to execute web request for the specified URL". I do not believe my Windows Credentials are getting passed to ServerA from ClientZ.
Is this a known bug or am I doing something wrong in my set-up?
Thanks,
bbossi
View 1 Replies
View Related
Nov 23, 2007
hi all
any one can tell me to avoid credential prompt i.e. uid & pwd when the first time going to render report from the reporting server
View 4 Replies
View Related
Nov 15, 2007
Hi, I am making a module to backup SQL Db from webpage. I impersonate asp.net user to a given Windows Account and need to verify that account has required permission in SQL Server.I don't know how to verify the windows account credential for SQL Server login. Now what I am doing is to run SQL query against database to select row from some table, if it is successful, the user is valid SQL login. But I know it's not the way. Any idea? Thanks,
View 1 Replies
View Related
Jul 9, 2014
Due to a previous (mis)configuration, i need to grant readwrite permission on a share from a MSSQL DB User.The SQL user will launch t-sql queries on demand and they cannot be scheduled.
I've created a credential object in SSMS, configured it with the correct AD user and mapped it to the MSSQL DB user.Now, if i execute a simple t-sql backup:
BACKUP DATABASE [DB] TO DISK = N'IP.ADD.RE.SSshareDB.bak' WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
I get an access denied error, monitoring the sqlserv.exe process via procmon, i see that the Sql Server process is not impersonating the AD user configured in the credential, it still try to access using the local machine account .
View 1 Replies
View Related
Sep 12, 2005
I am hoping to use SQL Server Agent 2k5 to run jobs in the security context of another account. I have successfully done this, so I know it works.
View 4 Replies
View Related
Oct 9, 2007
Hello,
Our department will create several SSIS packages that will contain connection managers that use the IBM or Microsoft OLE DB provider for DB2. In every case, we will have to use a specific user name and password.
We need one point of reference for the password for the multitude of packages in case the password changes. I'm guessing that we would do this with a single XML package configuration that each of these packages could use. If this is the case, how can it be done securely?
Are there other ideas that I should consider?
Thanks!
cdun2
View 7 Replies
View Related
Jun 8, 2007
Looking for opinions here on what is the best way to programmatically test that a password change to a credential has occurred successfully.
The password change was made using Alter Credential, and I can see in sys.credentials that the credential was updated by the tool that handles these updates. I would just like to be able to confirm that all of the credential updates were successful (in other words, they authenticate correctly) before anything starts to fail.
Assume that the number of SQL Servers is too great to manually check them even if the whole team worked on it together.
Thanks in advance!!
View 1 Replies
View Related
Sep 19, 2014
I need to give the below script which contains CREATE CREDENTIAL query to an app team.
CREATE CREDENTIAL crdntl WITH IDENTITY = '<service_acct>',
SECRET = '<pwd>'
GO
My concern is i don't want the password to be visible. Basically i want to use this credential to create a proxy which is then used to run SQL Agent backupjob on number of SQL servers. Also, i cannot leave the SECRET value as blank (as the MSDN suggests.)
Is there any way to mask the password OR any other alternative solution.
View 0 Replies
View Related
Nov 23, 2015
is any way to select SQL logins that are Mapped To Credentials as using this query I get only NULL:
SELECT credential_id FROM sys.server_principals WHERE name = 'test_login'
I see the list of Mapped Credentials in SQL login properties with GUI, but can not select this option.Â
View 6 Replies
View Related
Aug 12, 2014
We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?
View 8 Replies
View Related
Mar 6, 2008
I had a question about proxy account for ssis.
Is it necessary to set up credential with windows account for proxy account so that ssis package can be setup as a job in agent ?
without proxy account ssis package is unable to run under agent as a job ..but..
does identity in credential for proxy need to be windows account ?
View 4 Replies
View Related
Sep 28, 2015
I have a scenario where I want to make a linked server query and report using windows service account credential. I can able to do link query if I RDP into the Server where linked server established using the service account and run query successfully  but local client SSMS with my credential fails connecting linked server or querying. Looks to be a sql double hoping problem if so configuration each client domain account to enable delegation will be challenging as mentioned in the following articles instead service account only might work if possible.
View 3 Replies
View Related
Jun 15, 2015
I am looking command (cmd/powershell/c#) for setting/updating credential for SSRS data source.
View 2 Replies
View Related
Nov 12, 2006
I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.
It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))
SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END
Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?
Thanks.
View 3 Replies
View Related
May 23, 2001
When running a trace on a database, how does if affect that databases performance? Does it slow it down at all?
Thanks In Advance,
Chris
View 1 Replies
View Related
Feb 4, 2004
Hello,
What happens to these REPLication agents if SQL Agent is stopped and started:
Snapshot Agents
Merge Agents
Misc Maintenance Agents
Can the agent be stopped and started with no thought to the status of replication, or should the replication state be modified in some way before any change to the status of the Agent?
(I know REPL depends on agent, so wuestion is, can REPL simply resume or not when agent is re-started.)
Thanks.
MichaelGG
View 2 Replies
View Related
Jan 31, 2004
If the server is upgraded to Windows 2000, will SQL 6.5 require to be upgraded to SQL 7 or 2000?
View 1 Replies
View Related
Apr 30, 2007
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
View 1 Replies
View Related
Jul 7, 2006
How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?
Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.
I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...
I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.
Thanks in advance.
View 13 Replies
View Related
Aug 6, 2007
Hi..
I want to know that when I do something like..
Select Query
Left Join
Select Query 2
Left Join
Select Query 3
How does it work actually?
As in, whether Query 2 & Query 3 will work only on the records retrieved by Query 1 only.
Or, all the select statements retrieves all the records and then the condition is applied to filter out the results.
Also, does the order of the Select statements make any difference on the speed?
Thanks
View 4 Replies
View Related
Dec 7, 2004
We have a database that we have designed so all the data dependances are managed by the front end code. However the company we are writing it for has asked us to add around 50 constraints. I was just a little worried what kind of effect on performance this would have. Cheers Ed
View 1 Replies
View Related
Nov 16, 2006
Edit: Sorry This is OSQL.What I use as my query is:"DELETE FROM timerecord WHERE Actual_Time_In LIKE '11.12.2006%'"The row of Actual_Time_In is formatted with Date and time (MM.DD.YYYY HH:MM:SS) sometimes there are ten records and I'd rather not have to remove them from the table one at a time. However, even though I have a record that is '11.12.2006 22:43:00' my delete doesn't work osql states I have 0 rows affected.This is only MSDE so I don't have anyother way to open the table.Sometimes these records have other records that reference them. Is there anyway to do a cascading delete without it getting to complex?Thanks of all your help, I am just a tech support guy beating his head against a wall..
View 2 Replies
View Related
Feb 5, 2007
Hi all,
Does JOIN order effect efficiency?
If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).
Thanks in advance,
Chiz.
View 1 Replies
View Related
May 1, 2008
Hello all, thanks in advance for any advice here.
My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's?
Thanks for any guidence on the usage of this.
Bob
Not a downstroke, fistpicker
View 6 Replies
View Related
May 27, 2008
Hi Guys,
I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?
View 1 Replies
View Related
Jul 13, 2007
I'm looking into the automatic recompilation of stored procedures andI have been reading up on the "Do not recompute statistics" option onindexes.Am I correct in concluding that disabling the "Do not recomputestatistics" option for an index, will ensure that no automaticrecompilations will occur as a result of updates to data in thatindex?Am I also correct in understanding that the "Update Statistics" willstill update statistics for the index even if the "Do not recomputestatistics" option is disabled?RegardsBjørn
View 1 Replies
View Related
Jul 20, 2005
Hello All,I have a stored procedure which will act like a main/controller scriptwhich will then invoke more stored procedures (~20). Basically, itlooks something like below:-- start scriptcreate procedure ...print 'process started'exec sp_1exec sp_2exec sp_3....print 'process ended'-- end scriptLooking at it, after running that procedure, immediately I would expectthe first PRINT statement to be printed but it won't. It seemedthat the print statements would only display the messages afterthe whole processing completed.What's the reason for this sort of behaviour. If so, then we wouldnot be able to print any progress reporting in our scripts.Please comment.Thanks in advance.
View 4 Replies
View Related
Jul 20, 2005
Hello,When I try to run the following query on two different SQL Servers,I get error on one of the server (both has same set of data). I wastrying to get rows for ProductCode='XYZ_Q1'.SELECT ProductName, ProductType, ProductDesc FROM Product WHEREDepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,CHARINDEX('_', ProductCode)+2, 1) AS int)=1Example dataProductCode|ProductName|ProductType|DepartmentID|P roductDescXYZ_T_1|Test1|1000|12|TestXYZ_T_2|Test2|1000|12|TestABC_T_1|Test3|1000|11|TestABC_T_2|Test4|1000||11|TestXYZ_Q1|Test5|1000|12|TestABC_Q1|Test6|1000|11|TestIt's trying to cast all values under 'ProductCode' column instead ofapplying to subset with condition 'DeparmentID=12 AND ProductType >2000 'I solved the problem by equating it to whole string rather thantrying to extract the integer part of it. But I wanted find-out reasonas to why this is happenning.Is there any SQL Server setting that's causing this?Thank you very muchManchaiah
View 1 Replies
View Related
Sep 11, 2006
Hi,
I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for €˜public€™ role and individual sql logins). I€™m not getting any error messages. Permission changes just don€™t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.
How can I change the permissions?
View 4 Replies
View Related