Hi There
I just want to be 100% sure about something.
Certificates generated for use with service broker endpoints must be generated in the maste database, correct?
What are the implecations of the master key is changed for the master database ?
Thanx
Hello,
I created a HTTP-Endpoint on SQL Server 2005. Now I try to call this web service from a c# client but using SQL-Server authentication (i.e. my user is a sql server user but not a windows user). But it doesn't work.
I can connect to the service using a windows user and NTLM authentication. But for some reasons Iwould much prefer if I could use SQL-Server authentication. Is it possible?
Here is how I created the endpoint:
Code Snippet
CREATE ENDPOINT myService
STATE = STARTED
AS HTTP(
PATH = '/services',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( SSL ),
SITE = '*'
)
FOR SOAP (
WEBMETHOD 'GetStuff'
(name='MyDB.dbo.FKT_getStuff',
SCHEMA=STANDARD ),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'MyDB',
NAMESPACE = 'Services'
);
GO
Thanks!
Jerma
Hi There
I have service broker working 100% with an initiator - forwarder - target, all in the same domain using windows authentication on the endpoints, for all instances' the sql server service run under the same domain account, which in turn is granted connect on the various endpoints. This all works 100%
However i have a scenario where the initiator will be in a different trusted domain.
I need to know if i am correct in thinking that the initiator sql server servcie account can run under DomainAsqlservice, the target instance sql server servcie can run under DomainBsqlservice, then on the forwarder i simply grant connect on the forwarder endpoint to both DomainAsqlservice and DomainBsqlservice.
Is this correct, will this work?
Thanx
I created a HelloWorld like example using InforPath 2007 and endpoint with SQL Server 2005. Very simple example.
I created a stored procedure that took in one parameter, a datetime field. This parameter is then inserted into a db table that has two columns, an id column and a datetime column.
I took the store procedure just discribed to create an endpoint. Cool. The WSDL is showing, I'm feeling good.
I go to InfoPath and make it so that submit a datetime to the webmethod associated with the endpoint. The data table shows one more row has been added and it is the datetime I sent from InfoPath. YEA!! I go to the Event View and see that I have an error message. The message is HTTP authenticaiton failed. [CLIENT: 10.8.2.26]. Bummer!!
This does not make sense to me. I am able to update the targeted database table but I get an exception. I do not like exceptions nor warnings without an explanation. Does anyone have an idea why this is happening?
The endpoint code looks like: <code snippet>
CREATE ENDPOINT [GeraldTesting_endpoint]
AUTHORIZATION
STATE=STARTED
AS HTTP (PATH=N'/testEndPoint',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'*',
CLEAR_PORT = 8080,
COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'testEndPoint'
( NAME=N'[geraldstest].[dbo].[Gerald_Test_Proc]'
, SCHEMA=STANDARD,
FORMAT=ALL_RESULTS),
BATCHES=ENABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60,
DATABASE=N'geraldstest', NAMESPACE=N'http://tempuri.org', SCHEMA=STANDARD, CHARACTER_SET=XML)
</code snippet>
I had to give up for the project I am currently working because of time constraints. But in the future, for out-of-the-box functionality for proof-of-concept/prototyping, I would like to use endpoints.
I am just about ready to tear my hair out on this one, at this point you guys are my last resort! Wouldn't it be nice if I could go to bed and have an answer for me in the morning....
I've had a working HTTP endpoint using Basic Authentication over SSL for months in our development environment, but now I'm moving our application to a production server and I'd like to be able to use Digest Authentication in the clear instead so the processor doesn't waste cycles on the SSL decryption. I've spent hours fiddling with my endpoint to no avail.
This is the closest message relating to what I'm doing that I've been able to find: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1131251&SiteID=1
In my case, the SQL Server is joined to a domain that is entirely Windows Server 2003 - no Windows 2000, so support for Digest Authentication is a non-issue. I'm also explicitly doing a GRANT CONNECT on my endpoint to the domain user I'm using. Here is the top of my CREATE ENDPOINT DDL:
CREATE ENDPOINT [Kudos]
STATE=STARTED
AS HTTP (PATH=N'/Kudos',
PORTS = (CLEAR),
AUTHENTICATION = (DIGEST,NTLM),
SITE=N'echobase.kudosnow.com'
DEFAULT_LOGON_DOMAIN = N'kudosnow',
AUTH_REALM = N'kudosnow'',
CLEAR_PORT = 80,
COMPRESSION=DISABLED)
FOR SOAP (
I am debugging using wfetch 1.3... I can get this to work using NTLM Authentication; sending a GET to my endpoint with the ?wsdlsimple querystring parameter returns my WSDL just fine.
But Digest just doesn't want to work! If I remove NTLM from my AUTHENTICATION directive and try using Digest with wfetch or from my (PHP) web service consumer I get an HTTP 401 no matter what I do. I've tried fiddling with the DEFAULT_LOGON_DOMAIN, AUTH_REALM, and anything else I can think of.
The errors I am sporadically finding in the SQL Server error log are:
HTTP authentication failed. [CLIENT: 192.168.245.238] from source Logon
Followed by:
Error: 26026, Severity: 14, State: 1. from source Logon
I can't find any detail about the second error.
........
Just occurred to me that I've also been trying to view my WSDL directly in IE, and this is what's causing the SQL error to be logged, not wfetch - that's why it appeared sporadically.
So, wfetch isn't causing an 'HTTP authentication failed' error, but Internet Explorer is... maybe Digest Authentication isn't implemented correctly in wfetch?
Regardless... I'm still not able to authenticate from IE or from my client, but this may shed some more light on the issue.
Does anyone have any ideas?
Hi folks,
I have created an EndPoint in SQL Server 2005 as per the code below.
CREATE ENDPOINT OSTC_LMS_Endpoint
AUTHORIZATION LMSEndPointUsers
STATE = STARTED
AS HTTP(
PATH = '/ostc_sql_endpoint',
AUTHENTICATION = (BASIC),
PORTS = (SSL),
SITE = 'OSTC-DEV-001'
)
FOR SOAP (
WEBMETHOD 'ostc_SQLSoapTester'
(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',
FORMAT = ROWSETS_ONLY,
SCHEMA=STANDARD),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'OSTC_LMS_06',
NAMESPACE = 'http://tempUri.org/'
)
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [LMSEndPointUsers]
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [ostc-dev-001endPointUsers]
GO
-----------------------------------------------------------------------------------------------------------------
The SPROC being exposed as the webmethod: -
USE OSTC_LMS_06
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'ostc_SQLSoapTester' AND type = 'P')
DROP PROCEDURE ostc_SQLSoapTester
GO
CREATE PROCEDURE ostc_SQLSoapTester
@UsersId char(12)
AS
SET NOCOUNT ON
SELECT FIRST_NAME,FAMILY_NAME
FROM USERS
WHERE USERS_ID = @UsersId
GO
USE OSTC_LMS_06
GRANT EXECUTE ON ostc_SQLSoapTester
TO LMSEndPointUsers
GO
USE OSTC_LMS_06
GRANT EXECUTE ON ostc_SQLSoapTester
TO [ostc-dev-001endPointUsers]
GO
----------------------------------------------------------------------------------------------------------------
The computer in question is our dev server and is running as a workgroup machine with the following: -
Win Server 2003
SQL Server 2005
.net Framework 2.0
No firewalls or Proxies are in the way.
The computer has to be as a workgroup machine to reflect our live server.
The user LMSEndPointUsers is a SQL Server Login
The user ostc-dev-001endPointUsers is a machine login
We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.
----------------------------------------------------------------------------------------------------------------
Code used to connect and try and reciev the dataset.
string sUserName = "userName";
string sPassword = "Password";
LMS_Endpoint.OSTC_LMS_Endpoint wsSQLTester = new LMS_Endpoint.OSTC_LMS_Endpoint();
SqlSoapHeader.Security sqlSec = new SqlSoapHeader.Security();
sqlSec.Username = sUserName;
sqlSec.Password = sPassword;
XmlWriter writer = XmlWriter.Create("Security.xml");
writer.WriteStartElement("security");
sqlSec.WriteXml(writer);
wsSQLTester.sqlSecurity = sqlSec;
DataSet dsMySet = wsSQLTester.ostc_SQLSoapTester("RH6915145507");
Basically authorization is denied 401.
Hope someone can help here as have tearing my hair out.
Thanks in advance
With the new functionality provided by sql server 2005, can we use the certificate functionality to act as a db store for third party certificates. Posts that I have seen so far indicate that there is no direct access to the certificate store once a certificate has been imported to the db. Given that the database is more portable (for failover purposes for certificate storeage, lack of access to the certificate store should be considered a considerable problem for ISV's.
Failing that, if we need to impliment this functionality ourselves what is the best format to store certificates in the database (blob or other)
thanks
Mike
Hello:
I have access to a microsoft certificate server and I have generated a server certificate for use in my SQLexpress installation. The certificate was installed into the personal folder of the local machine. The MMC certificate snap in can see it fine.
When I use the SQL server configuration manager and look in the certificate tab and try to use the drop down to find the certificate so SQL express can use it, nothing shows up.
Can someone please give instructions on how I can get SQl express to use the server certificate that I just installed?
Thanks.
Larry
Does SQL Server support wildcard Certificates. When you install the wild cert in the certificate store, the sql configuration manager does not see it in its drop down list. Id it does, what are the steps or please point me to the right direction. Does the cert need to be specifically for that particular hostname. Thanks
View 1 Replies View RelatedI am looking for a good introduction into the handling of certificates in SQL 2005.
I need to sign a procedure to allow it to access a dm view.
I do understand the theory and the syntax, but I have trouble coming up with an easy but still secure way to create these certificates on all customer servers without allowing misuse.
All articles I could find are going through creating a new database, setting up a certificate with or without password, signing an example proc and then dropping the database.
Non seems to care about the problems that occur later on during the life of a certificate.
Thanks
Hi,
I want to get all the student from a Database table which store student certificate. For example, I need a query of student who "HAVE" 3 certificate(Cert A, B, C).
Certificate Table:
StudentName CertificateName
John Cert A
Wilson Cert B
John Cert B
John Cert C
Michael Cert A
Output:
John
sqlQuery = "Select * from CertificateTable Where (CertificateName = 'Cert A') AND (CertificateName = 'Cert B') AND (CertificateName= 'Cert C')"
This is my query, but it not works.
Calvin
Need to replicate mobile device that uses a wildcard certificate. Heard that ms windows mobile 5.0 does not support wildcard certificates. Is there a solution around this using vb.net.
https://myrep.domain.com/dbname/sqlcesa30.dll
Microsoft CF 2.0
Microsoft SQL Mobile 2005
Microsoft SQL Mobile 2005 Replication
Hi,
View 3 Replies View RelatedHi There
This may seem like a stupid question but i am trying to get the hang of the new security model.
I have not really heard anything mentioned about certficate expiration date when it comes to creating certificates for keys or service broker endpoints etc.
We have created certificates for keys and service broker endpoints, now what exactly happens when the expiration date, by default 1 year i think is reached, will we no longer be able to decrypt encrypted data and will the service broker endpoints stop working etc ?
Or is this expiration date when the certificate can no longer be used to create security objects ? And all security objects already created with this certificate will always work ?
In other words is there ever danger that keys and endpoints or basically any object referrencing this certificate will just suddenly stop working one day, or will all objects work indefinately regardless of an certificate/objects expiration date ?
Thanx
Hello Guys,
Here it is my scenario;
My principal server in LAN and my mirror server in DMZ and Iam using certificates both partners (I dont use witness server).
I did not use wizard.I have just Setting Up Database Mirroring Using Certificates (Transact-SQL)
(http://msdn2.microsoft.com/en-us/library/ms191140.aspx)
Iam sure all steps are ok!(I mean ports,certificates,users,logins,endpoints,GRANT CONNECT ON ENDPOINT,SP1....)
But I am still getting error Principal Server;
Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://195.xx.yy.zz:5022'.
Also,Mirror Database status changed (Restoring---->In Recovery)
I tried many tests but I did not succeed.Do you have any ideas in this case or any experince in database mirroring using certificates.
Many thanks for your reply...
Regards..
Tarkan G.
I am currently trying to replicate a SQL Mobile 2005 database with a SQL Server 2005 database through web synchronization using SSL Server AND Client Certificates. On IIS, with "Require Client Certificates" unchecked, I can replicate fine. Once I turn it on, I get a message from replication saying "A Secure Socket Layer connection is required to access this site". I have installed a client certificate in IE, and can access the https://servername/Ojt/sqlcesa30.dll site (I tried removing the client certificate, and I was denied access, then reinstalled it and it worked - so I think that part is working). Does anyone have any experience with this? My production operating environment requires client-side certificates.
View 5 Replies View RelatedHi, i want to know if its posible to create credentials or certificates in order to protect a SQL 2005 data base.
Because if someone Buckups one of my DBs from my server, and try to restore it in orther server i dont want they to see my DB information because he dont have the correct credentials or certificates for it.
This is posible?. if is, How i do it ?
Best Regards.
I am trying to use the example in http://support.microsoft.com/kb/915852. This creates two databases SourceDB and TargetDB. If I put SourceDB on the same SQL Server instance as TargetDB, the messages is received with no problem. If I put the SourceDB on another Server so than I am using two separate servers in the same domain, the message never gets to the TargetDB. I have changed the routes to the correct server names and set the route port to 8286.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'SourceService',
address = 'TCP://toto:8286';
and:
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'TargetService',
address = 'TCP://devbox05:8286';
My SourceDB is on one of several instances on the server toto. My instance is totofoxylady01,52005.
The certificates were generated using the passwords in the article.
Hi,
The project I'm currently working on has to be relatively simple for the users to install, so I had the idea of using service broker to "set itself up". The idea is that there will be a server with a service and suchlike already set up. Somehow the clients will get the information required to create a remote service binding, and once that has been accomplished will send a registration message to the server.
What I'm wondering is can I somehow create and send a certificate using service broker via t-sql, and also is this approach reasonable or is it a bad idea. The reason I had thought of something like this is because the people setting up the client sites may (and probably will) have very little knowledge (probably no sql server knowledge) so we need the installation to basically be a click and install.
Thanks in advance,
Adam
Any way to bulk export / import TDE Certs? I've got a bunch of databases that need to be moved to another system. Just about every database is using TDE and was wondering if there was a way to move these certs in a bulk fashion. I've got SQL and Powershell scripts to backup and restore multiple databases, but won't do me any good without the certs.
View 0 Replies View RelatedI am trying to set up SQL Server 2005 to use a test certificate from Thawte for SSL encryption. I have installed the certificate in the local computer, current user and service account's personal certificates folders. I have also installed the root certificate in the Trusted Root Certification Authorities folders of each. All this was done using MMC.
However, when I go to tell SQL Server to use the certificate, no certificates show up in the drop down box. I am using SQL Server Configuration Manager and doing the right-clickPropertiesCertificate steps shown in multiple KB and forum articles.
I have also checked the registry entry at HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibCertificate. It is blank, which tells me SQL Server should be looking in the certificate store.
If I simply set the ForceEncryption flag, SQL Server starts up OK and generates a self-signed certificate. Using that, the session does get encrypted. However, I need to use a third party certificate.
I have hit a brick wall and am at a loss. Any help would be greatly appreciated.
Hi
I am looking at documentation of CREATE CERTIFICATE statement. I am having hard time in understanding if I want to create CERTIFICATE with above mentioned options, how I am supposed to create either PRIVATE KEY file or EXECUTABLE file. any example would be really helpful for what I am doing here.
thanks
Satya
I folks.I Have installed sql server 2005 express and choosed windowsauthentication on instalation, but i make a mistake and now i needmixed authentication, how can i modify this whithout uninstall andinstall again the application?thanks for the help.
View 2 Replies View RelatedHow to give authentication for Send Mail Task component?
View 4 Replies View RelatedHi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 Replies View RelatedHello,
(Using win2k, sqlserver2k, framework 1.1)
I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
Any ideas??
Hi all,
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
Thanks.
Hi all,
My work is using a shared application
which accesses a MSSQL 2000 database. To access the application, the
folder on the Windows 2003 Server is shared and users can access the
folder through a shared drive.
For the application to access the
database, it uses an ODBC connection to the MSSQL server which
originally used the SA password.
We have recently switched to using
Windows Integrated Authentication because we believe it offers a
higher level of security. However the only way in which we have been
able to enable this is to add the windows users to the SQL server.
The problem with this is that the
application sets permissions for individual users on what records
they can see within the database. We have found that by adding the
windows users to the SQL Server, they can bypass the permissions the
set by the application by simply using any application that can use
an ODBC connection, such as Enterprise Manager, and see all the
database.
One way around this would be to set up
domains of users with access privileges to the tables which reflect
the permissions set by the application, and configuring a view of the
data so they may only see the records that they have permissions to.
However to do this would require a high administrative cost to ensure
that changes made in the application are reflected in the privileges
of the SQL server.
Instead, is there a way the SQL server
can authenticate that the ODBC connection is coming from the correct
application using Windows Integrated Authentication?
This would allow the applcation to
determine security, and stop users from connecting to the SQL server
using other applications.
Alternatively, can the SQL server,
using Windows Integrated Authentication, also ask the application to
supply a username and password?
Any help with this matter would be
greatly appreciated.
Thanks!
Hi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
View 4 Replies View RelatedHello
Can anyone tell me what is the difference between sql authentication and windows authentication.
Examples of each would be very useful
Many thanks in advance
Steve
Would anyone please help me out here. which of the 2 modes of authentication is better and why??
View 3 Replies View RelatedHi,
Say, I have configured my SQL to use Mixed Authentication. Now, I have a applicaiton which uses my SQL Server. The application just creates a database in SQL Server and uses the database to store its information.
This application also has a SYSTEM DSN under ODBC through which it accesses the database. For the application to access this database, should I only use SA (as my SQL instance is configured to use Mixed Authentication) or can I use Windows Authentcation too...
If I should only use SA, do we have a documentation which talks about this.
Thanks
Santhosh
For using different services of SQL SERVER 2005 which is better...
Windows Authentication or SQL Server Authentication?
what are the advantages and disadvantages of both?