SQL 2012 :: Moving SSIS Solution To Another User - Reset User Encryption Key
Oct 22, 2014
I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
We are facing issue on one of our databases on Availability Group replica. FYI, please refer the below messages we are receiving. Moreover, the errors don't happen consistently for the specified user.
The client was unable to reuse a session with SPID 420, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Login failed for user 'XXXX'. Reason: Failed to open the database 'YYYY' configured in the login object while revalidating the login on the connection. [CLIENT: aa.bb.cc.nn]
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK) WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
A user forgot her password, how do I reset her password? sp_password require a former password to change, and I'm unable to do that since she forgot her's
Hi... When i try to login(other than sa for e.g. "test1" user) in mssql server the account be locked. By using sa account i try to unlock the test1 user by using the following command
Alter login [test1] with PASSWORD = 'pass123' UNLOCK
when i execute above command test1 user unlocked successfully.
But our requirement is, with out setting the PASSWORD property i want to unlok the test1 user
I'm trying to determine what the best way to do what basically amounts to per user encryption is.
I want to provide the ability for my users to store their passwords (to various applications, and possibly product keys, but the content is mostly irrelevant) in a retrievable format, but I want this information encrypted at rest. I'm not new to cryptography but am fairly new to encryption in sql server.
My issue arises from not seeing how I can manage (creating) the keys for the different users in a fairly automatic fashion.
The closest I've come to what I'd consider to be 'workable' is having the user 'request' access at which point they'd be asked for a password to create their master key. That master key would be used for protecting individual keys for each of the stored items. If so desired it could then continue the key chain to allow users to share specific items. Sadly I don't have the infrastructure for a CA, otherwise I'd go that route as theoretically that should 'just work' if I can assign people certs.
Ideally I'd be assigning users to an active directory security group, which would be part of an 'allowed to use the password tool' server role with appropriate permissions, and the client would authenticate (or be impersonated) through to the database with their pre-existing windows credentials, and everything else would just be magic.
Back on topic, I'm mostly looking for a way to handle this initial key creation (i.e. without a user 'requesting access' and having to create their master key w/password through an interface or support request). I expect I'll have to write an interface for the end user to manage their individual keys and "sharing" regardless of the route I take, so adding a way for them to create the initial key isn't a concern, I'm just worried I may have missed something and will be reinventing the wheel here.
Is there built in functionality I can/should look at regarding this? Or does anyone have a better way to do what I want to?
....it's possible without any third party application?I need to recover some encrypted user functions but the sources have beenlost long time ago, someone can help me?--Lav.
I would like to force only certain users on certain databases to use encrypted connections for accessing our 2005 SQL Server. I've found a clunky way to do it, via registering a login event notification and using the kill statement. However, that's ugly, and I don't think there is any guarantee that the event will always fire before an evil client could get off a few evil commands. Is this supported, or is the only supported feature the forceencryption option, which is server-wide?
I saw an article I believe on this site, now I can't find on HOW TO MOVE USERS AND ROLES FROM ONE SERVER TO ANOTHER. DOES ANYONE KNOWS WHAT IS THE BEST WAY TO DO that?
Ok. So we just moved over one DB from our original server to another server. The DB copied over fine.
Our next step is trying to see if there is a way to move accounts. Is that possible?
We have about 40 accounts (using SQL authentication) on the original server. Is there a way to move or copy those accounts to the new SQL server that is holding that same DB?
Haven't found anything in the Enterprise manager, so im thinking it will be something to do in the SQL analyzer.
My lack of knowledge in SQL is not helping me much here.
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name. It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
My app works fine in development server. After moving to web server, error occured as bellow. Can someone help me? I use vs 2005 and SQL server is windows authentication mode. Login failed for user 'domain_nameREPORT$'. 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.SqlClient.SqlException: Login failed for user 'domain_nameREPORT$'.Source Error:
Line 348: Line 349: If MyTable = Nothing Then Line 350: MySQLDataAdapter.Fill(MyDataSet) Line 351: Else Line 352: MySQLDataAdapter.Fill(MyDataSet, MyTable)
In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
In cases when the SSIS package has to be deployed in SQL Server, when we use ProtectionLevel ="server storage" while creating SSIS Catalog, does it use TDE? or Database Standard Encryption? if the database is using TDE?
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
i'm using the Enterpirse library logger to write logs into a database. When choosing connection string i choose the database i want in the "connection properties" dialog box and push 'Test connection' button. everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things, from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore.... it only work when i restart the computer.
Cannot open user default database. Login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. 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.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!
Hi, I'm new to SQL Express 2005. I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue. I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....". Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively. My connection string is: "Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
Hi, I have a copy of VS2005 and i have installed the Microsoft SQL Server Developer Edition I am trying to do my first connection using ASP.net (yay for me...) and it has failed...with this error..Login failed for user 'testuser'. The user is not associated with a trusted SQL Server connection.
can someone please point out why? yes i know that the error seems pretty straight forward but how do i fix it? like how do i make the user a trusted server connection? where do i do it? and what are the steps? the user is an administrator and by rights should have all access to everything....
connection string
SqlConnection MySQLConnection; MySQLConnection = new SqlConnection("server=cmptrname\databsename;uid=testuser;password=password;database=mytest");
btw while your reading do you have any examples of opening a db with the connection string in the web.config file? because i get some error "connection string has not been initialised" using the system.Configuration.ConfigurationManager.AppSettings.Get i though i would stick to the top code first before i tackled the web config error first....
I need help. The security principal is the correct DomainUser, but the error message says I am not authenticated. So here is my error Message: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Me.User.Identity.Name=CompanyDomainRyan; System.Security.Principal.WindowsIdentity.GetCurrent=CompanyDomainRyan; HttpContext.Current.User.Identity.Name=CompanyRyan Generated by: Dim secPrinc As System.Security.Principal.WindowsIdentity = System.Security.Principal.WindowsIdentity.GetCurrent Me.lblError.Text = ex.Message & vbCrLf & vbCrLf & "Me.User.Identity.Name=" & Me.User.Identity.Name & "; System.Security.Principal.WindowsIdentity.GetCurrent=" & secPrinc.Name & "; HttpContext.Current.User.Identity.Name=" & HttpContext.Current.User.Identity.Name Here is the Connection String: Private ConnString As String = "Server=10.144.162.111;Database=DNS;Trusted_Connection=True"
IIS has 4 websites hosted on it. The parent level allows anonymous with windows auth off, but the website level has anonymous off with widnows auth and digest. In IIS, is there a problem with having the parent level anonymous and website level windows auth?
All the research I found dealing with this problem is that the solution is to set SQLserver to mix mode. I have SQL server already set to mix mode. I am not sure what else to do. Has anybody run into this problem? my connection string: <connectionStrings> <add name="ConnectionString" connectionString="Data Source= server name ; Initial Catalog=FILESHAREDB.MDF; Integrated Security=false;"providerName="System.Data.SqlClient"/> </connectionStrings>
Server Error in '/SendItNow' Application.
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.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.Source Error:
Line 41: Line 42: 'open the connection and execute the stored procedure Line 43: mConnection.Open() Line 44: mResourceID = mCommand.ExecuteScalar() Line 45: mConnection.Close()Source File: H:MIS DepartmentIntranetSendItNowApp_CodeDalResourceDB.vb Line: 43 Stack Trace:
Hi all, I am new in asp.net. In my web.config file, I wrote the connection strings as below and then I got the above error. I already changed SQL server Authentication mode to "SQL server and Windows". Any idea? Thanks in advance. <connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="Data Source=xxx;Initial Catalog=mydb;User ID=test;Password=test;Persist Security Info=False;" providerName="System.Data.SqlClient" /> </connectionStrings>
Our server has integrated security setup, upon startup of the server, we have a continuous flow of error msg: Login Failed: Reason: User '_' not defined as a valid user of trusted connection. The timing of these messages only around 30 seconds apart The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ? I recycle the server but didn't help. I appreciate any help I can get .. Thanks.
I'm using the RTM version of Visual Studion 2008 and SQLServer Express.
Logged in as an administrator, I can create a connection to an mdf file and all is well.
As a normal user, attempting to create a connection gives the message "sqlservr.exe has stopped working A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available."
I have tried this with UAC turned on and turned off. The result is the same. In Services I can see that SQLEXPRESS is running, but each time I try to connect, I get the "stopped working" message.
For existing database programs that have the .mdf file stored in the project folder, I can run the program in the VS IDE on my XP machine and on my Vista machine as administrator, but not as a normal user.
I'm not looking for a workaround to make this work once, but the cause and solution to the problem. This is for an introductory VB textbook and we must be able to move a database program from one machine to another and make it work. Everything I have read says that SQLExpress should be able to do that.
I am trying to migrate reporting services from SQL server 2000 to SQL 2005. I followed the migration steps listed in one of the TechNet documents. Installed SQL 2005 and SP2.
Backed up existing SQL 2000 databases and restored on SQL 2005.
Went to Configuration tool and then to the Database setup. Connected to SQL 2005.
Clicked on the Upgrade button. The upgrade fails and I always get the following errors:
"The database version (C.0.6.54) does not match your reporting services installation. You must upgrade your Reporting services database"
Then I get this exception:
System.Data.SqlClient.SqlException: Cannot find the user 'RSExecRole', because it does not exist or you do not have permission. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
I do not understand why it is looking for user 'RSExecRole', which is a role.
My goal is to write a DR plan where i am restoring all user databases onto a diffrent server in a event of hardware failure. I was trying to figure out a way to extract DDL of user accounts and their permissions on all user databases so i can simplify my DR documentation.
This is the plan I came up with...to restore all system and user dbs on a different Physical SQLServer.
1. build named instance $PROD
2. restore master database
- startup sqlserver in single user mode -m or DAC sqlcmd -S ServerName -U sa -P<xxx> –A net stop MSSQLSERVER$PROD net start MSSQLSERVER$PROD -m - restore database master from disk e:master.bak with replace;
3. start sqlserver normally
4. stop SQLServer agent
5. restore msdb
-restore database msdb disk e:msdb.bak with replace;
