Howto Reinstall With Local Encrypted Data, No Domain?
Aug 17, 2005This is a question more about EFS, I think, than SQL itself, so apologies for that.
View 1 Replies
ADVERTISEMENT
SQL Server Admin 2014 :: Cannot Decrypt Encrypted Columns From Database Backup On Local Machine
Jun 29, 2015I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.
USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]
I tried doing above still no luck.
Domain Vs Local
Jul 20, 2005What are gotchas for starting Sql & the agent with a Local system accountversus a system Domain account.
View 3 Replies View RelatedLocal Vs. Domain Account
Feb 17, 2006Hi All,
How can I tell how SQL Agent is configured to start up with? Is it with the local system account or domain account?
Thanks.
Domain User Or Local System Account ?
Jul 20, 2005Hello,My server is part of a W2K domain. What do you advice me as account torun my SQL*Server, service started with a domain user account or aslocal system ?I need advices from a security point of view.Thank's in advance
View 4 Replies View RelatedNeed To Establish A Remote Connection To An Sql Database On A Local Server (same Domain)
Feb 16, 2007Hi, I have been creating a web store, which we will be hosting on our Windows Server 2003 system. I am also writing a windows application that will be running on our client systems on the same network/domain. I have no need to allow internet access to the database, just within our domain. Pretty much the windows application will be running and whenever a customer places an order, the program will automatically pop-up the needed information for our credit card processing, and shipping department. So far I have been able to establish a connection to our server by creating a data connection under the server explorer. However, it does not list any databases whatsoever, the web site has been published using Visual Studio 2005 Professional on the server, and is in 100% working order. I have searched and haven't found anything concrete on this, i'm sure its just something simple that im missing. It appears that the server is definetely connected to the database (since the website is fully functional), but it is not listing the information for other systems to access it. If anyone has any ideas, please let me know. Thank you
View 8 Replies View RelatedDomain Users + Local Groups + Scale Out Deployment = Problem
Feb 13, 2008We have a problem authenticating domain users contained in local machine user groups across multiple web servers in a scale out deployment.
When we originally setup our single SSRS database server we were told the a best practice is to add domain users to local user groups on the SSRS machine.
Now we want to add more web servers and create a scale-out deployment. So, we added the web servers and configured the scale-out deployment. But, only administrators can see the reports since all of our SSRS roles are assigned permissions such as "Machine1User_Group".
We were told that we have to create identical local groups on Machine2 and Machine3 and then add them to the SSRS roles. This is prohibitive since it would mean managing 3 identical user groups containing thousands of domain users.
Is there a better way to do this without using Domain User Groups?
Thank you for any assistance.
None-Domain Server Cannot Access SQL2005 Data On Windows 2003 Domain Server
Sep 26, 2006I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.
- I have try following the KB265808 - no success.
- Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.Error Message:
TITLE: Connect to Server
------------------------------
Cannot connect to ardsqldatawh.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
Question: Could Windows 2003 security be blocking access? I'm using sa account to access.
Also, sa account does not seems to work for remote access. It is ok when accessing locally.
Any help would be appreciated.
949jc
Reinstall SQL Server Without Loosing The Data
Mar 26, 2002hi Everybody,
for some reason my Sql Server has stopped and i am not able to restart it at all, do i have to reinstall the complete SQl Software or is there ne way i can start the MSSQL Server
Writing Data To Multiple Tables (Howto)
Sep 23, 2006Can someone show me the command string required to write data out to 2 or 3 tables at once? How about how to write 2 entries at once? Write now my solution writes 1 record to a table and then I write somewhere around 40-200 records to a second child . Write now I'm writing like this:Open --> Add 1 record in table #1 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeand I'm just wondering if there is a better approach, such as writing all the data at once. Something like:Open --> Add 1 record in table #1 --> Add 5 records in table #2 --> close
View 1 Replies View RelatedDo Managed Local Accounts Remove Need For Multiple Domain Accounts
Aug 12, 2015I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account. It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective. Several still advocate multiple accounts. I don't know if they are doing so because that's the way it's always been done or if there is still some compelling reason to do so. I don't want to create unnecessary accounts simply because something is "ideal."
View 8 Replies View RelatedSQL 2012 :: Master Data Services - Delete / Reinstall Web Application
Oct 6, 2015I have a bad web application install for Master Data Services. This was the first installation and there was some back and forth to get it going. I now have set up an identically configured server correctly and figure the easiest thing to do is delete the web app and re-create it rather than spend a bunch of time troubleshooting. How do I do this? I've googled and haven't had much luck. I only want to uninstall the web app since everything up until then was installed correctly.
View 1 Replies View RelatedIs Data Sent Via DTS Encrypted?
Jan 27, 2005Hi all,
I may have a requirement to send data from a SQL Server at site A to an Oracle server at site B. These sites have no network connection between them, and the current suggestion is to use ftp, but the transfer (or username and password) will not be encrypted.
If I create a DTS package transferring data from site A, will that transfer be encrypted?
If not, is there an option with SQL Server DTS to ensure that the data is sent in an encrypted form?
Thanks in advance.
Encrypted Data
Feb 23, 2004Hi,
How can I save encryped data in SQL2000? Also, how can I retrieve it?
Login For Domain Local Group And Global Group
Jan 5, 2008I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
Searching Encrypted Data; Using MAC Secret Data
Aug 10, 2006I just finished reading an article on how to search encrypted data efficiently and they suggested creating a new column with a Message Auhtentication Code. To be honest, reading the aritcle makes my head hurt. I can hardly understand what they were doing myself and I can't begin to explain it to a developer.
Are there any easier ways to search encrypted columns for a speciifc match? If not, does any have some stored procs that implement this messy MAC stuff?
TIA,
Barkingdog
Howto Get Avoid Bulk Insert Data Conversion Error?
Aug 7, 2006hi, i having a problem in bulk insert , which is regard the text file that
to insert into database, when insertion processing,
if my textfile have NULL value, it give me Bulk insert data conversion error
for example in my text file c:mytest.txt , it contains data NULL
123 studentname NULL
can we let bulk insert detect NULL value ?
i have try on putting "KEEPNULLS" , but it doesn't help , caused some fields in table may in datetime type
BULK INSERT [mytable] FROM c:mytest.txt WITH (FIELDTERMINATOR = '' '', ROWTERMINATOR = '''', KEEPNULLS )'
thank you
AdventureWorks In Management Studio Is Gone: How To Reinstall Its Database && Data Warehouse From Script Or By Downloading
Oct 28, 2007Hi all,
One year ago, I did "Ruuning Setup to Install AdventureWorks Sample Databases and Sample" to install "AdventureWorks" in my SQL Server Management Studio Express. Now, It just have the name "AdventureWorks" on and no contents (no tables, schemas, etc.) in it. In my Control Panel, Add or Remove Programs, AdventureWorksDB (162.00 MB) is still there and AdventureWorks (Windows Installer Package [27,992 KB]) is in my C:/drive.
(1) Should I delete the name "AdventureWorks" in my SQL Server Management Studio Express, and remove AdventureWorksDB (162.00 MB) from the Add or Remove Program, before I re-install AdventureWorks, AdventureWorksDW, and Adventure Works DW sample databases and samples from Windows Installer Package [27,992 KB]? Is my old AdventureWorks (Windows Installer Package [27,992 KB]) still good and complete for installation?
(2) SQL Server 2005 Books Online (September 2007) presents "Reinstalling Sample Databases from Script". But (i) I can not find the file instawdb.sql for reinstalling AdventureWorks from script and I do not have AdventureWorks OLTP in my C:Program FilesMicrosoft SQL Server90ToolsSamples folder, (ii) I can not find the file instawdwdb.sql for reinstalling AdventureWorksDW from script and I do not have AdventureWorks Data Warehouse in my C:Program FilesMicrosoft SQL Server90ToolsSamples folder either!!??
Please help and tell me what is the best way and some detailed instructions/tips to have AdvenureWorks, AdventureWorksDW, and Adventure Works DW sample databases and samples installed in my SQL Server Management Studio Express.
Thanks in advance,
Scott Chang
DB Design :: How To Use Encrypted Data In Oracle DB
Jun 26, 2015I am using a sql server 2012 and having a database "Test".In this database , having a table "Employee". I have encrypted the employe table data with the password. How can is use these employee data in oracle server. ie;How can I show the employee data as the encrypted and decrypted form of data in Oracle server( The actual encryption happened only in SQL DB).
View 8 Replies View RelatedSQL 2005, Replicating Encrypted Data?
Jun 1, 2006Hello,
I've been searching for information on this for awhile unsuccessully. I am using SQL Server 2005 Enterprise Edititon and transactional replication with separate publisher, distributor and subscribers. I need to encrypt data on our publisher and then I need to replicate that data out (i.e., encrypted data, not encrypted transmission). Since the keys are server specific, I imaging there might be some difficulty in doing this, but I figure I can't be the only one with this problem. Does anyone have a resource or know what must be done to get this accomplished?
Thanks,
Aaron Lowe
Backup And Resore Encrypted Data
Mar 27, 2007I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
-----------------------------------
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@id int,
@var1 varchar(100)
AS
BEGIN
DECLARE @var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@var1 )
insert into dbo.Test(id,Data,EData)
values(@id,@var1,@var2)
CLOSE SYMMETRIC KEY symkey_demo
END
---------------------------------
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@id int
AS
BEGIN
DECLARE @var1 varbinary(256)
DECLARE @var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @var1=EData from Test where id=@id
Set @var2 = convert( varchar(100), DecryptByKey( @var1))
print @var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
Handling Encrypted Data In SSIS
Mar 11, 2008
Hi, I have source data in encrypted format. How should i handle it in SSIS?
I have found no information for such situation.
Anybody have any idea about it.
Bhakti
How To Protect Encrypted Data When DB Is Stolen
Jun 25, 2007I got a problem concerning encryption. The thing is I have decided to use symmetric key protected by certificate to encrypt certain information. Certificates are protected by database masterkey and by service key.
But I also want to be sure that if someone steals my database with all its data he wont be able to decrypt it with his own SQL Server Management Studio where he has all the permissions.
Also after some time I will need to take my database and set it up on another PC.
Has anyone ideas how to solve this??
P.S. As far as I know if symmetric key is protected by certificate which is protected by DB master key and service master key then you cant decrypt data if database is moved to another workstation and opened with another Management Studio. Please can anyone explain how this works( if its true). And if this is true then how can i move my DB without loosing access to encrypted data???
Domain Administrator Vs. Local Administrator Group
Jul 16, 2002We are trying to set up SQLAgent Proxy account. If the SQLServer service id is a domain admin, do we still have to add it to the local administrators group?
Jeff
Domain Account Vs Local Account For SQLServerAgent
Jul 20, 2005Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedSearching For Encrypted Fields In Data Columns
Jul 20, 2005I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s
View 10 Replies View RelatedMAC Used As Search Index For Encrypted Data: How Secure?
Aug 17, 2006I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?
TIA,
Barkingdog
Backup Database With Encrypted Column Data
Jan 25, 2007Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.
How could I backup/restore master key, symmetric and asymmetric key created for my database?
Thank you
Reporting Services :: Error While Deleting Encrypted Data
Sep 15, 2011I am using SQL Server 2008 R2.I have opened the Reporting Service Configuration Manager to delete the encrypted data.In the encryption key section, when I click on the 'Delete' button to delete the encrypted data, it gives me below error. Microsoft.Reporting Services. WmiProvider. WMIProvider Exception: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.DeleteEncryptedInformation()
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
SQL 2012 :: Encrypted Data In Project Manifest Failed To Load
Sep 8, 2015An inherited SSIS (2012) solution displays the following messages when I try opening it in VS 2012:
"One or more projects in the solution were not loaded correctly."
Then another message displays
"The encrypted data in the project manifest failed to load. The project manifest is corrupted or the project was encrypted by another user."
EVENT ID 18456 STATE: 16 Login Failed For User 'DOMAIN/user'. [CLIENT: &&<local Machine&&>] Every Minute In Event Log.
Oct 22, 2007We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
How Can I Retrieve Sql 2000 Encrypted Column Data From SQL 2005 Stored Proc?????
Aug 4, 2006
Hi...
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
Thank you.
Bal.
Whether To Use Local System Account Or Domain Account For Service Account
Jan 5, 2006During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View Related