I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.
To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?
Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network
Any help or pointers would be greatly appreciated.
Setting up Service Broker routes, endpoints and security is just too hard. One has to run pages and pages of Transact-SQL code just to get the Hello, world example work between two separate SQL Instances and the chances of making a mistake are overwhelming. Well, not anymore! Ive just uploaded into the Service Broker team code gallery a new GUI tool for doing just that: easily configure two services to be able to have conversations. The tool uses the Service Listing concept. A Service Listing is like an identity card for a service. It is an XML document that contains all the necessary information needed to establish a conversation with that service. When two parties need to establish a conversation, they can exchange the Service Listings of the two services and the tool will create the entire infrastructure needed to establish the conversation. Optionally it can also create the message types and contracts supported by a target service in the initiator service database....
I must prepare a procedure which should export some data from some DB columns, but these data must be encrypted using the OpenSSL protocol, to be sent to an external recipient.This recipient sent to me a "public" certificate in PEM format, to what I understood (it is a file beginning with the expression "-----BEGIN CERTIFICATE-----", then a long Base64 sequence and then "-----END CERTIFICATE-----").
Then I didn't create the certificate nor I am a certification authority: I just have this public key (or, at least, a file from which I can extract a public key) and I must use it to encrypt data in order to obtain an output which must be analogue to what I can obtain when using this OpenSSL command line instruction:openssl rsautl -encrypt -in CF.txt -out CF.enc -inkey SaniteICF.cer -certin -pkcs
Then the question is: starting from this PEM file and nothing else, can I import it and store an asymmetric key or a certificate in SQL Server and use it to encrypt data to be exported?
i m trying to send message between different server instance using service broker.
and for security purpose i am trying to create certificate. for that i have used makecert.exe and get a certificate and a private key. but when i am creating certificate using that file it is showing error
the code is --
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:SourceServer.cer'
WITH PRIVATE KEY ( FILE = 'C:SourceServer.pvk', DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO
i have created the file SourceServer.cer' and SourceServer.pvk' by using makecert.exe tool.
the idea behind creating the certificate ctfSourceServerMaster is to give transport security.
I am running the particular script in the master database.
but still i am getting error
ERROR:----
The certificate, asymmetric key, or private key file does not exist or has invalid format.
 We are unable to login in database due to âThe server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid. Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.âwe have tried to run that selfssl.exe from command prompt followed by below command and am getting the cryptographic error.
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.
Following script runs fine on SQL Server.
Code Snippet
use master
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk'
ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'
Create Certificate EndPointCertificateC
WITH Subject = 'C.Server.Local',
START_DATE = '06/01/2006',
EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE EndPointCertificateC
TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
Following script runs on SQL Server Express:
Code Snippet
Create Certificate EndPointCertificateC
From FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
WITH PRIVATE KEY (
FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk',
DECRYPTION BY PASSWORD = '45Gme*3^&fwu'
);
If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. I'm suspect there's a simple answer, but I'm unaware of it. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is impersonating a low-privilaged Windows account ("UserX") coming from a .NET application. UserX has no permission to the table referenced in the dynamic SQL and because of the dyanmic nature of the query, the stored procedure apparently adopts the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may use the same connection, and be as secure, but simpler.
I'm looking for some sample code rather than having to re-invent the wheel.
I need to write an analysis services stored proc that will invoke a SQL stored prod in my DW dataabse to retrieve a list of client_id's. I then need to construct and return a set object. This AS stored proc will be referenced from a role.
I've read few things about using the Set object rather than StrToSet function but then the only way I see to create a Member orSet object from a literal is to build an Expresion object and call the CalculateMdxObject(null).ToSet() function. Is this not equivalent to MDX.StrToSet()?
Also, ideally I'd like to connect to my SQL db by accessing the connection string from the Data Source objects in my AS DB. So far I have not found a way to do this.
Some guidance and sample code would be much appreciated.
I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?
I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?
I've created a custom dll using ADOMD.net that takes in a few parameters (Username being one of them) and returns a set of Allowed Members to apply dynamic dimension security. Example:
Code Snippet
Security.GetSetList(UserName,1,"SQL Server")
This works fine for users with less than, say, 100 tuples/members in their set....however for users with more an error:
Error ocurred retreiving child nodes: The 'S' attribute in the 'S' dimension has a generated dimension security expression that is not valid. ...
However, if I take the same set and explicitly (ie
Code Snippet
{[S].[1],[S].[2],.......[S].[50000]}) put it in the Allowed set there's no issue!
I noticed that it takes the UDF a couple of seconds longer to return for users with a large security context. Is there a time-out parameter that controls this? Is there a limitation in the amount of members I can add via custom dll (UDF)? (Some users can have up to 70,000 members)
I should also mention that the UDF uses the StrtoSet function to convert the security context string to a set. The string can be as large as 210,000 characters. Is there a limitation on the string size that can be passed in this function?
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID] in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.Â
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent  "Tom" in APAC region, the numbers will look like this APAC_Sales = 100,000 Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales    = 200,000 Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
I have two different roles, each one with a dax filter. One is for filtering users that access by Excel, and other for filtering users that access by Reporting Services, respectively:
To create only one role that serves Excel and Reporting Services users, is it viable to use only the || (OR) operator?, is there any other regard i should take?
Can anyone HELP!! I have a system set up with 2 servers, the master server replicating the data to the backup server. Each server has two network cards, 1 which links directly to the other server. The problem is the replication is being routed round the network making the direct link useless. How can this be ammended!! They are both logging into the same primary controler.
Is there a way to route a file directly to a printer? I have a process that creates PDF file in a known location and I would like to route them directly to the printer after creation.
We are currently starting new project and evaluating possibility of using Service Broker in it but seems there could be some problems We have a central server with a central database and several hundred of mobile? users. Information ( actually a subset which is specific to a particular user ) should be sent from/to central database and we wanted to use Service Broker for this. When you set up a route in Service Broker you need to define Network address which I believe should be an IP address or DNS name. The problem for us is that our mobile? users do not have static IP addresses and I am not sure we can assign a unique dns to all of them. Mobile users can connect to internet in different placesŚ and hence get different IP addreses. Is there a solution to this problem? Rsgards, Leonid.
Unable to access the 'T' database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation. Changed database context to 'T'.
I have 2 sql servers running in an AG group. SQL 2 is fully syncd and is used for read only reporting.The connection from the listener using ReadOnly works and connects to SQL 2. The problem is, when a database in SQL 2 is suspended from the AG group, the listener does not connect to the SQL 1 (primary).
The AG group is setup exactly like this:
Readable Secondary = Yes for both SQL 1 and 2, Availability Mode = Synchronous
why the listener does not connect to primary server?Connection in primary was set to "Allow read/write connections" for the AG. This meant "Connections where the Application Intent connection property is set to ReadOnly are not allowed. " Setting to "Allow All Connections" worked.
I setup an AG with a listener (AO1) on three servers.
Server1 = Primary, Readable Secondary = No Server2 = Secondary, Readable Secondary = Read-intent only Server3 = Secondary, Readable Secondary = Read-intent only
Connecting to AO1 with default settings, I end up on Server1.
If I attempt to connect to AO1 with specifying ;ApplicationIntent=ReadOnly (with or without specifying the db name) in SSMS, still lands me on Server1.
Also on topic, in this scenario I would actually want Server1 to be a readable secondary (in the event of a fail-over). So If I did set it as Yes, even If I do get the issue above working, won't I always land on Server1 then?
My application supports a single database connection and in the app console I can produce reports. If I include the app database in an AlwaysOn availability group with a read-intent replica will SQL automatically route the âselectsâ to that second instance thus offloading my applicationâs reporting activities or I need a separate db connection (maybe from a reporting app or cli) with a connection specifying read-only intent?
Having an annoying AG/AO problem with the read only routing side of it.
Let me give some specifics first:
2 SQL Server Instances, Not Clustered. Availability Group is named 'Ireland'
There is a primary Replica and a Secondary Replica, named:
'IrelandPrimary' and 'IrelandSecondary'
There is a listener configured with the name 'ListenIreland' on Port 14330 (the two 3's are correct)
Read Only Routing URLS are configured as follows: IrelandPrimary tcp://Ireland.dom.local:49891ALL IrelandSecondary tcp://Ireland.dom.local:49841ALL
So now my problem:
When I try to connect using the ApplicationIntent=Readonly; or even using -K ReadONLY in sqlcmd I get the error telling me that my connection was actively refused.
This is connecting to the Listener, not the instance itself - that works fine. I'm at a bit of a loss now.
To explain what I am trying to achieve is a for a connection to be redirected to the secondary replica when its set for read-intent.
I've just noticed that it only fails when I specify ApplicationIntent=ReadOnly; If I omit the Intent It connects to the read-write database instead.
this failed, what am I doing wrong or did I lost the plot? How do I diff beteen more than one instance of sql server on the same server? And can the port be the same on each instance of SQL Server?
I am trying to create a encrypted row in my database Everything here worked except that when i run the final query to decrypt the data It just comes up with null for each row. Even if i do a query to show me the rows that are not null It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.Here is what I tried from start to finish Create Certificate TestCert Encryption By Password = 'Password' With Subject = 'SQLCert', Expiry_Date = '12/01/2050';
declare @Test nvarchar(50) set @Test='123456789'
insert into testenc (testencry) Values (encryptbyCert(Cert_ID('TestCert'),@Test ))
select convert (Nvarchar(50), DecryptByCert(Cert_ID('TestCert'), testencry,N'Password')) As Test from testenc
im trying to drop all certificates on my database, and then the master key, but cannot do this as there are objects encrypted by one particular cert that i called fcert. I done this ages ago and cannot remember what i encrypted with this cert. to drop the master key i have to drop the cert and to drop the cert i have to make sure no objects are encrypted by it. how is this achieved?
I can use "CREATE CERTIFICATE" to add a CER certificate into a SQL database. How can I do so with a PFX certificate? From the document, it can be done so with a CER file and a PVK file, but only a PFX file.
hey, I am having a weired issue(donno whether its weired or not.). I have a user who has db_owner rights on a database. But when he is trying to create a certificate he is getting error. "Msg 15247, Level 16, State 1, Line 1 User does not have permission to perform this action." Remember he is having db_owner rights on that particular database. is there any other permission that i have to give him.
