The option:
SQL Server Configuration Manager>SQL Server Network Configuration>Protocol for SQL_xxx (right click)>Properties, we can see two Tabs:
Tab 1: Flags
Tab 2: Certificate
If I set the value of Hide Instance= Yes, does "Force Enctyption", need to be set YES as well?
Or Force Enctyption has to be enabled in order to hide instance?
What is the recommend settings?
[URL]
How does one enable sql connections limits for user connections per new and existing databases? how to do it on whole server per database but not set a limit per user.Looks like this must be run on each databases but what if you have 100s of databases:
USE AdventureWorks2012 ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'user connections', 325 ;
GO
RECONFIGURE;
GO
How can i Trace Database Audit specification Enable and Disable. i want to maintain log for enable or disable database audit specification.
View 4 Replies View RelatedIn SQL Server Management Studio, it is possible to do the following:
a) In Object Explorer, connect to a particular SQL Server Database Engine, let's call it X.
b) Use that connection to generate many SQL queries, connected to X, in the right-hand pane.
c) In Object Explorer, connect to a particular SQL Server Database Engine, let's call it Y.
d) Use that connection to generate many SQL queries, connected to Y, in the right-hand pane.
e) Close the connection to X, which was created in step (a), from within Object Explorer.
In the right-hand pane, I am now left with a bunch of queries connected to X, and a similar bunch of queries connected to Y. Any quick way to shut all the queries connected to X, but none of the queries connected to Y?
This question can assume real practical importance if server X is a Live Production Server, and server Y is a Test Server, and my next job of the day is to run a change script against server Y....
I have looked for an option in Management Studio to "Close all queries connected to Server X", but haven't found one.
I have 2 SQL 2008 R2 Servers which are always running (on is Prod/one is Test).
is there a way to sync Prod to Test WITHOUT using the .bak and overriding Test's Security settings?
Hello guys,
I would like to know how to enable remote connections to my SQLExpress installation? Thank you very much for any help!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
How to enable remote connections by code without "SQL Server Surface Area Configuration" ,implement by .net framework?
View 1 Replies View RelatedI have a customer who is looking to encrypt their applications connection to the back-end SQL server. I've gotten a valid certificate for the server from a trusted CA, and it's installed on the server (shows up in Personal certificates in the Certificate MMC when selecting the computer) and I can choose it in the drop down on the certificates tab of the SQL Network protocols properties in Configuration Manager.
Now, my question: If I select the certificate but do NOT enable the "Force Encryption" option, the client will be able to request encryption on their end, and the connection will then be encrypted. If the client (other applications) do NOT request encryption, they will still be able to connect, but their connection will be un-encrypted. URL....
Lastly, I could've swore I had found some information indicating that some methods of establishing a connection to SQL can fail when Force Encryption is enabled, as the method doesn't support SSL encryption.
Hi guys
Apologies if this is the wrong place to be posting this but I have posted on a few forums and have not had any usable answers to this question, so I am hoping you guys will be able to help me.
I have 2 servers that are on the same domain. My ASP.NET application is running off the first which has .NET 2.0 installed, and I have an SQL Server 2000 db on the other. Whenever I try to connect I get the following error: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)
The connection works when I run the project in VS Development Server on my work computer, but as soon as I upload to the server I get this error.
I have scoured Google but all of the sites I come across are for SQL Server 2005 and are just not suitable for solving the issue on 2000.
Any help would be greatly appreciated as I cannot continue my current project until this issue is resolved.
Hermiod
On my SQL Server 2014 SP1 on Windows Server 2012 R2, allow inbound TCP 1433 and UDP 1434 seems to not be enough for managing remotely any named instance on this server.
View 8 Replies View RelatedI have a strange situation when I try to execute the same Stored Procedure on servers with different processors. Both servers are running the SQL SERVER 2008 R2 version with all updates.
All updates bios, disk controller, firmware, were applied on the new server.
New Server: (considerable difference in processing time)
Stored Procedure without encryption, runs at about 02:16hs
Stored Procedure with encryption, runs at about 08:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.60GHz E5-2697 v3
Old Server: (There Are No difference in processing time)
Stored Procedure without encryption, runs at about 01:00hs
Stored Procedure with encryption, runs at about 01:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.7GHz E5-2697 V2
In terms of configuration, the server 2, have lower technology, lower bus, lower number of processors.
I believe that combination of Hardware/SO/Sql Server has a potential performance loss when running encrypted SP.
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key
encryption by password = 'testAppleA3';
create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test
drop certificate test
drop master key
Then recreate the master key:
create master key
encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
again but this does not resolve the issue.
Thx.
Hi,
Assume I have an asp.net/sql server 2000 web app in a shared hosting
environment. I then encrypt the connection string using
ProtectSection("DataProtectionConfigurationProvider") in the page load
of my default.aspx page.
Am I understanding the following concepts then correctly?
1. I upload the site to the shared hosting server.
2. The first time I run the app eg. www.whatever.com/default.aspx,
the ProtectSection method above is executed.
3. Now the conn string area of my web.config is encrypted, and
asp.net will decrypt as needed.
4. If someone were to hack the server and view the web.config --
whether via getting into the server or via ftp, they would see an
encrypted connection string.
Thanks very much!
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!'
CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs'
CREATE SYMMETRIC KEY ERKEY
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
Email
-------
1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
But, how do I work around this?
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
Hi there,
I just upgraded from SQL2000 to 2005 and noticed that any DB engines you register do not get re-registerd when you restart the Management Studio, you have to reconnect every time you start Management Studio! SQL2000 used to keep your server connections.
Am i missing something or is this just how it works now!?
Thanks
Ben
I have an auto exec stored procedure that needs to complete successfully or:
- the server should shutdown, or
- disable remote connections
Officially I cannot issue a Shutdown from a Stored Procedure. In addition, I can't see how to programatically disable remote connections.
Ideally, 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 - I have developed a database using Windows Authentication in Management Studio (CTP) but now need to set up logins for users externally - crucially NOT using windows authentication. I want to resrict these new logins to only run specific stored procs and completely deny all other privileges.
I've created a login 'bc_user' but when I try and connect using management studio (ctp) I get:-
Cannot connect to [server_name]SQLEXPRESS.
Login failed for user 'bc_user'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452).
(Connecting in code from VS produces a less specific message).
My user creation T-SQL looks like this:-
USE master
go
CREATE LOGIN bc_user WITH PASSWORD = 'test_pwd', DEFAULT_DATABASE = testdb, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
go
USE testdb
go
CREATE USER bc_user FOR LOGIN bc_user
go CREATE ROLE restricted_users
go
EXEC sp_addrolemember 'restricted_users', 'bc_user'
go
USE testdb
go GRANT EXECUTE ON dbo.example_stored_proc TO restricted_users
DENY ALTER ON dbo.example_stored_proc TO restricted_users
DENY CONTROL ON dbo.example_stored_proc TO restricted_users
DENY TAKE OWNERSHIP ON dbo.example_stored_proc TO restricted_users
DENY VIEW DEFINITION ON dbo.example_stored_proc TO restricted_users
I have searched for this error code and it is documented as a 'bug' in SQL 2000, fixed in SP2. Any ideas what might be causing this or what I have missed? I there a setting in SQL Server (express) that causes this? I have both TCP/IP and Named Pipes connections available and Windows authentication works fine from both VS and Management Studio (CTP).
Thanks
David
SQL Server 2008 R2: How security log is much secure than application log?
View 3 Replies View RelatedHi there
I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.
Can any one tell me how to enable that option please ?
I Forgot for my longtime used home expense update application password which has backend sql expressedition database.
i was used the application before 3 years, unfortunately i stopped updating my home expendature to the software.and now i require to login the application but i dont how reset the password in db, i have open database include tables of users profile. and password, but its encrypted.
Hi everyone,
I am currently reading ASP.NET unleashed and practising few examples. The following code converts a user's text into a symmetric encryption:
'nd: define keys
Const DESKey As String = "ABCDEFGH"
Const DESIV As String = "HGFEDCBA"
'nd: convert string to byte array
Function convert2ByteArray(ByVal strInput As String) As Byte()
Dim intCounter As Integer
Dim arrChar As Char()
arrChar = strInput.ToCharArray
Dim arrByte(arrChar.Length - 1) As Byte
For intCounter = 0 To arrByte.Length - 1
arrByte(intCounter) = Convert.ToByte(arrChar(intCounter))
Next
Return arrByte
End Function
Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
Dim arrDESKey As Byte()
Dim arrDESIV As Byte()
Dim arrInput As Byte()
Dim objFileStream As FileStream
Dim objDES As DESCryptoServiceProvider
Dim objEncryptor As ICryptoTransform
Dim objCryptoStream As CryptoStream
'convert string to bytes
arrDESKey = convert2ByteArray(DESKey)
arrDESIV = convert2ByteArray(DESIV)
arrInput = convert2ByteArray(txtInput.Text)
objDES = New DESCryptoServiceProvider
'pass keys
objEncryptor = objDES.CreateEncryptor(arrDESKey, arrDESIV)
'create to file to save password
objFileStream = New FileStream(MapPath("secret.txt"), FileMode.Create, FileAccess.Write)
'pass in file and keys
objCryptoStream = New CryptoStream(objFileStream, objEncryptor, CryptoStreamMode.Write)
'pass in text
objCryptoStream.Write(arrInput, 0, arrInput.Length)
objCryptoStream.Close()
lblDone.Text = "Done!"
End Sub
It works fine. But, how to i save this encrypted password into a database field instead of a writing it to a file? Also, could some please tell me how to paste code into this forum? I tried <code></code> tags but it did not work.
Many thanks,
Kevin
I have an encrypted database in server A. The reporting service is running on server B. I deployed all my reports to Server B. When I run the reports, I got the following message:
The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database.
I googled the problem, some said to backup the key, but when I opened the reporting service configuration tool to backup the encrypted, it said I needed to restore the key first. Some said I need to delete the key, but that defeated the purpose of an encrypted database.
Hi,
I have encrypted some columns of a table in a database. Following is the method which i applied for encryption.
I created a master key with a password and it is also encrypted by service master key.
Now i created a certificate without password, so it is only encrypted by master key of the database.
Now i created a symmetric key encrypted by the above certificate.
The data is encrypted by this symmetric key.
To decrypt data i use DecryptByKeyAutoCert.
On my server this encryption & decryption is working perfectly.
But when i take this database to another server, it is not working.
What is the solution for this, should i drop service master key to encrypt master key or is there any soln.
Thank you.
Pls give me soln. i am worried abt it.
Gaurav
I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:
OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION
BY CERTIFICATE [XXXX_CERT];
select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> ))
FROM Database2.dbo.TABLE1
CLOSE SYMMETRIC KEY [XXXXKey];
What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?
Hi,
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
I have inherited a database and looking to upgrade it from 2008 to 2014. I have imported the database into db projects but it has flagged warning s straight away because one of the previous contractors has created a bunch of stored procs with encryption.
View 0 Replies View RelatedI queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.
Does that mean that all those 4 DBs are encrypted ?
How to check if the database itself is encrypted or only the backups ?
Note :- I can see backups of key in a particular folder.
Hi,
Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.
Kind regards,
Jan.
Hi All,
I am newbie in SQL Service broker. As we know, the is_broker_enabled can help us check whether the specified database is enabled for service broker. I would like to know
<>How can I enable servce broker for specified database if I see the value(is_broker_enabled) is 0?
<>How can I disable the service broker for specified database is I see the vlaue (is_broker_enabled) is 1? Do I need to dropped all service broker configration in the specified database?
Thanks in advance.
Michael
Hi all,
I am developing an application for a big office which uses SQL Server 2000.
Apart from my database, on that server, there are two databases by other companies.
The administrator also has access to server but the client only wants him to backup the database.
I have two questions:
1) First of all (if it is possible) I would like to protect my own database from the other companies.
I don't want them to:
see the data in the tables (around 20 tables)
make changes to the stored procedures (more than 100 stored procedures)
be able to backup the database
2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden.
I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.
Thanks for your patience reading this!
Would really appreciate some help on any of these
Nicolas
I have a following problem. I have installed the
Microsoft Search Service. It is visible in the Service
Manager and is shown as running. The platform is a
Windows XP professional machine running SQL Server 2000
developer. I am using the service off the local host. I
can not enable the Full-Text search on the database as
that field is grayed out in the tools menu. Having read
the KB article 270671 I tried to use the stored procedure
sp_fulltext_database 'enable' to achieve the desired
result, but recieved an error 7609 Full-Text Search is
not installed, or a full-text component cannot be
loaded. At least according to the Service Manager the
Microsoft Search service is up and running. Any ideas as
to what is causing this. Thanks a lot.
--eugene