InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:40:50 GMT20http://www.sqlservercentral.com/Forums/Topic1303798-1526-1.aspxHello,I haven't done this in awhile and I think I am forgetting something.I have a table of users in one database.I need all the users in this table to have dbo access to another database.I created a role in the second database (database2). I gave it dbo schema.I then tried to assign all of the users in the usertable from database1 the role that I had created:DECLARE @sql NVARCHAR(4000), @user VARCHAR(100), @role VARCHAR(100), @DB sysname, @DELIM VARCHAR(4), @BigSQL nvarchar(4000);set @role = 'MyNewrole'set @user = 'NewUser'set @DB = 'Database2'set @DELIM = ''''SET @sql = ' IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE [name] = N'''+ @delim +@user +''''+ @delim +') BEGIN CREATE USER ' + @user + ' FOR LOGIN [' + @user + '] END; EXEC sp_addrolemember N''' + @delim +@role + ''''+@DELIM+', ''' +@DELIM+ @user +@DELIM+ ''';';SET @BigSQL = 'USE ' + @db + '; EXEC sp_executesql N''' + @sql + '''';PRINT(@BigSQL);EXEC(@BigSQL); I am getting an error:Msg 15007, Level 16, State 1, Line 6'NewUser' is not a valid login or you do not have permission.Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75User or role 'NewUser' does not exist in this database.I'm sure that I have done something like this before. I'm sure I am forgetting something.If anyone could point me in the right direction, I would appreciate it.Thanks,TonyMon, 21 May 2012 18:42:50 GMTWebTechie38http://www.sqlservercentral.com/Forums/Topic1303434-1526-1.aspxI am trying to do following task and I donot know it is possible in this setup or not?I am trying to setup performance counter on a non-domain server (Server1)All the performance counter values I want to capture in other sql server (Domain\Server2)In this task I donot want to establish any kind of connectivity from Server2 to Server1 (sql or windows), but I want to push data from Server1 to Server2. - I have a sql login (monitoruser) in Server2 with sysadmin role - and created a ODBC connection (perfmon) in Server1 using monitoruser (successfully tested) - created a datacollector set on Server1 running as local administrator (localMachine\sqlmon)When I an trying to start this collector set, it throws "Error: Call to SQLAllocConnect failed with %1" on Server1On Server2 Eventlog says, Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xx]I have both the server running in mixed mode (I also restarted sql services on both)I can connect Server2 from Server1 using SSMS with monitoruser loginWhat else I am missing here?Thanks in advanceMon, 21 May 2012 08:59:17 GMTDaxesh Patelhttp://www.sqlservercentral.com/Forums/Topic1304994-1526-1.aspxGood morning everyone, ran into somewhat of an odd question and thought i'd run it through the community:Background: sql 2005 and 2008 r2 environment, mix of both. Logins for all instances are always groups, example adventureworks_dbo (has dbo permissions), adventureworks_readonly, etc, etcI keep running into the issue of users getting permissions they shouldn't do to being added to groups. For example adventureworks_dbo is a group in active directory, but instead of simply having users in there it also has other groups, thus groups within groups, within groups (think of the movie Inception, that is the environment of permissions....)So someone who is not a DBA adds john smith to a group, but it so happens that group is also in adventureworks_dbo, so mr john smith without knowing it gets dbo access to a production database.How can I audit something of this nature? If it was just users and not groups I could use policy based management to lend a hand, but being that it's only active directory groups i'm at a lose for options?And thoughts are appreciated, thanks again.Wed, 23 May 2012 08:22:10 GMTjdillon 62969http://www.sqlservercentral.com/Forums/Topic1303484-1526-1.aspxI am running in an environment with multiple domain. I am looking for a way to automate scripting my database users so they can be backed up. My issue is that I can script the user to get something like this:[code="other"]IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyUser') CREATE USER [MyUser] FOR LOGIN [DomainA\MyUser] WITH DEFAULT_SCHEMA=[MyUser];[/code]The issue is that I cannot determine a way to identify DomainA dynamically. What happens if MyUser is in DomainB? Is there a place to look for that? I've tried sys.database_principals and sysusers, but no luck. Any ideas?The script needs to support SQL 2008 and SQL 2008 R2.Thanks in advance for your help!Mon, 21 May 2012 10:00:03 GMTDSquaredhttp://www.sqlservercentral.com/Forums/Topic1302039-1526-1.aspxHello SQL Server Central friends,I am assisting our web developer in setting up a standard for connecting to internal database servers from both inside and outside of the DMZ.Currently we have a mixed bag of SQL logins, Windows AD with ODBC, Machine logins mapped to roles, and anonymous access to replicated data.I would really appreciate any feedback regarding the best practices for websites authenticating to SQL Server in both scenarios.Also, if you could provide a link to an explanation or a brief description for setting up the recommendation that would be most helpful.Thank you in advance for your valuable input!HawkeyeDBAThu, 17 May 2012 11:42:18 GMTHawkeye_DBAhttp://www.sqlservercentral.com/Forums/Topic1302364-1526-1.aspxHiI'm trying to set up Windows-based SQL logins on an existing SQL 2008 installation. The installation was created by cloning a previous virtual machine, and where and how [i]that[/i] was set up, only God knows...The New Login screen can't seem to see the list of domain users. I browse to the domain, it can see that; it can see a Users node under that. But the node is empty. If I change the "Location" to the Users node under the domain, and type in the name of a (known, existing) domain user in the "Enter the object name", and then press "Check name", it gives an error.The same thing happens when I go to Services and try to change the SQL Server service startup account to a domain account (not that I actually want to do this, just to test whether anything on the machine can browse domain users).I don't know much about networks, and the person who does is being... let's say, not helpful. Anyone have any idea why this is happening?Fri, 18 May 2012 03:44:34 GMTseb 30496http://www.sqlservercentral.com/Forums/Topic1301835-1526-1.aspxHi Everyone,I have create a master key and am trying to create a certifiacte and keep getting this error "Msg 15581, Level 16, State 3, Line 4Please create a master key in the database or open the master key in the session before performing this operation."Below is my code to create the certificate. IF NOT EXISTS (SELECT * FROM sys.certificates WHERE [name] = 'config_cert') CREATE CERTIFICATE config_cert -- AUTHORIZATION enc_user WITH SUBJECT = 'X.509 encryption certificate for securing configuration symmetric keys.';GOCode to create the master keyIF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE [name] Like '%DatabaseMasterKey%') CREATE MASTER KEY ENCRYPTION BY PASSWORD = 1234xyz!';GOWhat am I doing wrong?Please help.Thu, 17 May 2012 08:55:29 GMTShabnam Guptahttp://www.sqlservercentral.com/Forums/Topic1092750-1526-1.aspxHiHow to deny permission to user for generating databse script in sql sever 2008 or 2005.I don't want to allow the user to generate script for a database like this1.Right click on the database2.Select TASKS3.Select GENERATE SCRIPTSIs their any way to do thisUser is a member of db_datareaderthank you in advanceWith regardsDDWed, 13 Apr 2011 05:53:10 GMTdilipd006http://www.sqlservercentral.com/Forums/Topic1299880-1526-1.aspxHi all,I have the following problem:There are several database roles on a database. Active directory users within our domain are not directly assigned to the database roles and they also do not exist explicitly as a login on the sql server instance. Rather, users are assigned to the database roles via active directory groups which are assigned directly to the database roles. (I think this is the most common way to do this.)Now, I want to list all the implicit assigned database roles for a user logged in by integrated windows authentication. Due the fact that only the AD group is available in the system tables, bit not the AD user, I cannot query them directly. Does somebody know how to retrieve the database roles that are assigned to a AD user indirectly?Thx a lot!BernieMon, 14 May 2012 14:42:33 GMTbernie.nhttp://www.sqlservercentral.com/Forums/Topic1286268-1526-1.aspxChanging jobs. My personal desktop box hosts my development sql server instances. My domain account from the old company that was mapped to sa is inaccessible now on my home workgroup. Short of uninstall / reinstall sqlexpress are there any other ways to get control of the sa account? If I could just reset the password I'd be a happy camper.Thu, 19 Apr 2012 02:53:40 GMTBSavoiehttp://www.sqlservercentral.com/Forums/Topic1283623-1526-1.aspxHI Team,i want all logins and groups in a instance in sql server 2008 R2 can you please provide script for this i can use sp_help logins, sys.syslogins, it is correct for to get all logins or not. for groups what should i do.txSat, 14 Apr 2012 05:21:40 GMTshiv-356842http://www.sqlservercentral.com/Forums/Topic1282842-1526-1.aspxI've got a dumb question... so dumb, I don't know how to ask it, what key words to search, or where to start reading. I'm not even sure this is the right board!None-the-less... here goes. I just need to be pointed in the right direction... I have this database, it has about 800 or so tables and about as many views from a financial transaction platform data model that I don't control.I have end users that would like to have a little "freedom" in creating their own reports and such (and an rabid internal auditor that wants to be able to access data without IT controlling what he sees). It is completely unreasonable to assume that the end users understand table linking and table cardinality...especially on a database so large. God help me if we have teach them about Left outer Joins.So... what I want to do is create "layers"... views over the top of the model the present "most commonly accessed" data in a flat structure that allows me to present a simplified data model. I thought I would even go so far as to put those objects in a seperate database, call it MiniMart (for the lack of a better name), and then have those views reach over to the other database to return data. Then the user is presented with a less daunting 20 objects, instead of an overwhelming 2000.use minimartgocreate view customer_detail as select customer_name, account_nbr, yada, yada from datamart.dbo.customer cust inner join datmart.dbo.accounts acct on blah=blahgoHere is my dillema. If I create views in my minimart database, give users access that database and the views I want them to have... their user would still need rights to the datamart database for those views to access the data in the datamart database...right? If I had an adventurous user with a copy of Excel, he could browse databases, and get to my underlying objects... and defeat the purpose.Surely there is a way to create a layered object structure that would allow me to create objects that accessed unerlying objects without actually allowing the end user direct access to them? (And not have to set up seperate instances of SQL Server with linked servers in between)Am I crazy?Thu, 12 Apr 2012 17:16:20 GMTjchapmanhttp://www.sqlservercentral.com/Forums/Topic1297979-1526-1.aspxHi,I'm just doing some testing with database roles and database level permissions and was wondering what (if any) the differences are between:~ the permissions obtained via membership of the fixed role db_datareader compared with permissions obtained via "GRANT SELECT TO [AUserOrRole]"~ permissions obtained via membership of db_datawriter compared with database level "GRANT INSERT/UPDATE/DELETE"Thanks.Thu, 10 May 2012 09:31:20 GMTMatt 776http://www.sqlservercentral.com/Forums/Topic1252739-1526-1.aspxHi All,I am a newbie DBA working with SQL Server 2008 Express. I am having a strange problem that I have not encountered before on my other databases.The way I understand it, if my particular user account is a member of the domain administrators group in my domain, and the domain administrators group is a member of the local Administrators group on the server I'm concerned about, and the SQL Server instance on that server has the login BUILTIN\Administrators granted sysadmin rights, shouldn't my user account then, by following the chain of permissions, be a sysadmin on that SQL Server instance?The issue that I am experiencing is that this is not happenning on the instance I am working with. I had to have a co-worker explicitly add my domain user account as a login on SQL Server and grant it the sysadmin privelege in order to have those rights on the database.If anyone has experienced something weird like this regarding security, I'd love to hear from you.Thanks.Wed, 15 Feb 2012 13:58:51 GMTpdonleyhttp://www.sqlservercentral.com/Forums/Topic1294085-1526-1.aspxI have two domain accounts used as SQL service accounts, Domain\dev and Domain\prod. When I execute sp_Send_DBMail with the @query parameter as Domain\prod, I receive the following error, but I don't receive the error under the Domain\dev account. The accounts have the same local and domain privileges.Error: Could not obtain information about Windows NT group/user 'Domain\User', error code 0x5.Through researching this issue, I have tried adding the prod user to the following groups with no success, Windows Authorization Access Group, Pre-Windows 2000 Compatible Access, and the Local Admins group. The issue seems to point towards a problem with executing the SP, xp_logininfo 'Domain\User'. I confirmed I get a very similar error when I run this command as Domain\prod. Once again, if the service is running under Domain\dev, the SP executes successfully.I also made a copy of the dev user to test with, but the copied user heeds the same errors as prod.I am not sure what is causing the user to need this access, as this user is running our production environment with no problems (except for this one).All suggestions or comments are welcome.Wed, 02 May 2012 11:57:56 GMTsudonjnhttp://www.sqlservercentral.com/Forums/Topic1291803-1526-1.aspxI have a user receiving "The user does not have permission to perform this action." when trying to expand Management in SSMS. The server she is trying to expand Management on is a server that was upgraded from SQL 2000 to SQL 2008. It's on SP SP3 CU2. I've granted the user read access to both Master and MSDB and db_ssisoperator on MSDB. Specifically they are trying to see some legacy DTS packages that we have on that server. I'm not even getting to the point where she sees the Legacy entry under Management. She does see "Data-tier Applications" and "Policy Management" but that is all that appears before the error.Thanks for any helpKennethFri, 27 Apr 2012 13:46:25 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic1295071-1526-1.aspx[font="Verdana"]Hi Folks,Can we skip all other old auditing/security techniques (Physical copy tables/Different Audit columns in each tables etc) to utilize the currnet/modern type of auditing/Security features introduced in SQL2K8 e.g. CDC, Track Changes, Audit Traces ... and so on? With a very keen focus on the performance/practicality of techniques!!!!The Business and the application holds sensitive data and requirement is to figure out each and every operation done by any user by any time :)So what you think what should serve the purpose optimally!Please comment!!!Thank you[/font]Fri, 04 May 2012 01:22:42 GMTAbrar Ahmad_http://www.sqlservercentral.com/Forums/Topic1294300-1526-1.aspxI needWed, 02 May 2012 19:00:51 GMTSam Greenehttp://www.sqlservercentral.com/Forums/Topic1294278-1526-1.aspxSolved my own problem. Post deleted.Wed, 02 May 2012 17:40:27 GMTRandy Rabinhttp://www.sqlservercentral.com/Forums/Topic1292594-1526-1.aspxIn my environment I have a sql server 2008 database on 64bit architecture. The production database accessed by users is using sql authentication. Currently I can have one security login mapped to one database user which can log into the database multiple time simultaneously. I have a need to restrict this ability. Can I do so by specific security logins or database users? If so where can I set this feature?If it isnt possible to do this by the security login or database user can it be set at the database level?Mon, 30 Apr 2012 09:17:52 GMTkwoznicahttp://www.sqlservercentral.com/Forums/Topic1293228-1526-1.aspxI've worked at some larger organizations in the past that had teams of SQL Developers who wrote in house hardening and auditing tools. However, if your in a smaller organization that is housing sensitive data, what are the leading tools that would be helpful in this area for SQL Server?Tue, 01 May 2012 09:21:42 GMTAbrahamLeftJoinhttp://www.sqlservercentral.com/Forums/Topic1292903-1526-1.aspxI have a very interesting issue, that I suspect is a bug, but have been unable to confirm, and was hoping someone might be able to shed some light on the subject.I have a user, whose user account is a member of an Active Directory group (let's call it 'SQL_DB_DBO'). That group has been granted membership in the db_owner role on a particular database.First, if I review the user mappings for this user at the server level, it shows no access to the database in question, but nonetheless I know he is able to access it. This is confusing, but not a problem, but does suggest to me there was something lost in the thought process.The true problem is this: As a member of the group that supposedly grants db_owner role, the user is unable to enumerate the permissions on the database objects. However, once I explicitly grant the use membership in the db_owner role, permissions can be viewed (and presumably changed).We are using SQL Server 2008.Any insight would be greatly appreciated.Mon, 30 Apr 2012 20:17:16 GMTelohirhttp://www.sqlservercentral.com/Forums/Topic1289274-1526-1.aspxHello, I have a situation in where I have one db user (let's call it webuser) that has access to multiple schemas in a database. So far everytime we created a schema, we've been granting this user access to it. However all of the schemas that webuser accesses, has the same owner (webobjects). Is there a simplier way we can set permissions so that webuser can access anything owned by webobjects (without using EXECUTE AS or IMPERSONATE everytime)Tue, 24 Apr 2012 12:29:24 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic1283902-1526-1.aspxHi guys,i need a solution to protect .bak files with Password.Anyways i know there is WinZip, but i need something else.Any suggestions on what to try?Mon, 16 Apr 2012 00:54:03 GMTTHE-FHAhttp://www.sqlservercentral.com/Forums/Topic1284450-1526-1.aspxI am working in a dev environment and need to disable kerberos for some debugging and testing.I ran [b]setspn -l server.domain [/b]and got a list of all SPN's for the server.I then ran [b]setspn -d MSSQLSvc/server.domain domain\serviceAccount [/b]to remove the MSSqlServer SPN.I then ran [b]setspn -T domain -Q */server.domain[/b] to query if there were any SPN's associaited with MSSQLSvc that I missed. there were none.Finally in ran [b]KLIST PURGE [/b]to remove all cached tickets.But when i connect to this machine via TCPIP i am Still being connected via kerberos ( visible when i check the sys.dm_exec_connections dmv)I have restarted the sql server service but that has not helped. has anyone any ideas what i am missing?EDIT: I have also tried restarting the actual server, but that did not resolve the issue.Mon, 16 Apr 2012 13:26:03 GMTwinston Smithhttp://www.sqlservercentral.com/Forums/Topic1285007-1526-1.aspxHi, I've created a custom database role for developers in my company, this role has enough permissions for everyday tasks such as creates, alters, inserts, deletes, updates, view definitions and some other permissions that let them create and modify any database object. We want to implement this role with a set of selected permissions in order to have a better administration. Right now they are in the role of db_OwnerI've made some tests on my desktop before implementing this role in a production server. I've created a SQL user to do some tests and also I've added this user to my custom role in a test database, but everytime I want to create a new table from the GUI in SSMS, it displays a warning message saying that "You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own.", despite of this, I'm able to create a new table, when a I save it, another warning message appears saying "You are not the table owner", after clicking OK, the table is created correctly, the same happens when I do right click on a table and select "Design", then I add a new column and save the changes and one more time the warning message is displayed.But when I Create or Alter a table via Transact-sql, it doesn't display any warning message. This warning message will be kind of annoying for developers who are used to the GUI.I've made some research about this message and it seems to happen with the db_ddladmin role, but nobody has come to a solution yet. Here's what Microsoft people says http://connect.microsoft.com/SQLServer/feedback/details/467582/db-ddladmin-role-doesnt-allow-use-of-design-functions-in-ssmsI hope you can help me out, thanks :-)Tue, 17 Apr 2012 09:04:54 GMThiram.osirishttp://www.sqlservercentral.com/Forums/Topic1283750-1526-1.aspxdear friends,I need to let my customer to access data in sql database via only my application. I dont want any passwords to be observed by profiler or etc while my application connects. if possible I dont want them, even with management studio, see or manipulate data. import or export data nor schema.I read a lot about encryption, users, etc.. but what do you suggest ? it seems that playing with user rights is best solution. but even server administrator must not see data nor change it. as you can imagine my application always should have access to data and the schema.what do you suggest. I got lost while playing with user roles. can you tell necessary steps to make a user to access only one database ? looks like everybody knows sql security very well but I'm new to it. I never had problems about security until now.best regards...Sun, 15 Apr 2012 10:08:05 GMTaykut canturkhttp://www.sqlservercentral.com/Forums/Topic1283549-1526-1.aspxI didn't think opening a database up to all users would be this hard, but I'm getting nowhere fast so I figure I'll post here.I have a database that I want to allow anyone to select from certain tables. To clarify, by "anyone", I mean anyone that can already login to the server. There are other databases I want to remain secured, but this one, named "training" I want to be accessible to all. I'm using SQL Server 2008 R2 SP1 if you're curious.I have granted SELECT and VIEW DEFINITION rights on the appropriate tables to the public role, and thought that would be it. What I now see is that users can connect and select from those tables, however they can't expand the database in Object Explorer to see a list of what the tables are - they get an error saying the database is not accessible when they try.I tried granting VIEW DEFINITION rights to public at the database level and this had no effect either. Anyone have any ideas what I'm doing wrong?Fri, 13 Apr 2012 18:14:14 GMTcaptbob007http://www.sqlservercentral.com/Forums/Topic1284490-1526-1.aspxHello,We need to revoke permissions to a group of people defined in Active Directory. No problem, use a role. But.... The head developer wants to make an exception (himself). How do I deny to a role, but grant to 1 person in that role.Mon, 16 Apr 2012 14:04:03 GMTgeorge.aucklandhttp://www.sqlservercentral.com/Forums/Topic1281679-1526-1.aspxOur data warehouse group is using Informatica (latest version) to pull data from many system, including my SQL Server 2008R2. We were using only Windows authentication until this came up. They insist that only SQL Server authentication will work. Why must Informatica use SQL Server authentication to pull data from a SQL Server 2008 database?Can anyone give me an example of how Informatica will connect with SQL Server Authentication?I am grateful for any help here.Wed, 11 Apr 2012 09:32:45 GMTAlan Spillerthttp://www.sqlservercentral.com/Forums/Topic1280639-1526-1.aspxHi,I have a database server installed with MSSQL Server 2008 (R1) on a non-standard port. On our client machines, we use SQL Server Configuration Manager tool to create SQL Server Alias. So when connecting from client using Management Studio, we don't have to specify the non-standard port number. All our client machines have got the client version of Microsoft SQL Server 2008 (R1) installed. This setup works well from Windows XP and Vista clients.Recently, we have added a new client machine that runs on Windows 7. It is installed with the client version of Microsoft SQL Server 2008 R2. We have similarly created SQL Server Alias on the new client machine. Unfortunately, the Alias does not seem to be recognized as the client is not able to connect to the SQL Server using Management Studio. On the other hand, if we specify the port number along with the server name (as in <server>,<portno>), the connection is successful.Does anybody know what could be the problem and how to resolve it?Any help will be much appreciated. Thanks!Tue, 10 Apr 2012 01:14:12 GMTmui.lan.chiahttp://www.sqlservercentral.com/Forums/Topic1279069-1526-1.aspxWe have a linked server that is set up between 2 production servers and I'm wondering what permissions are used. The same LS is used for all connections between these 2 servers. There are many different combinations that could exist. For example: Server1.DB1 could have permission to use Server2.DB2 using linked server LS1Server1.DB3 could have permission to use Server2.DB4 using linked server LS1Can a user that has permissions in Server1.DB1 access Server2.DB4 using LS1? Does any user that has read permissions in Server1.DB1 have read permissions in Server2.DB2 or do they need to use LS1 to access Server2.DB2?Thanks,JamesThu, 05 Apr 2012 12:15:17 GMTJamez36http://www.sqlservercentral.com/Forums/Topic1272370-1526-1.aspxBelow is quote from 'Microsoft SQL Server 2012 Pocket Consultant':[quote]Do not use INFORMATION_SCHEMA views to determine the schema of an object. Instead, query the sys.objects catalog view to determine the schema of an object.[/quote]Can somebody confirm it is true and explain/point me to source why is it recommended? I always use INFORMATION_SCHEMA, except where I can't.Many thanksSun, 25 Mar 2012 11:58:00 GMTmax(rockatansky)http://www.sqlservercentral.com/Forums/Topic1072061-1526-1.aspxI am having trouble with my SQL server not using Kerberos. I read Brian Kelly's article on sqlservercentral.com, but I STILL have problems...Here's our setup: SQL Server 2008 R2 Named instance using a static port Windows 2008 R2. The server is is in Domain[b]X[/b]. Our network admin manually registered the SPN for the Domain[b]Q[/b] via this command: setspn -A MSSQLSvc/servername.Domain[b]X[/b]:port# Domain[b]Q[/b]\SQLIDWhen I ran setspn -L Domain[b]Q[/b]\SQLID, the spn was showingHowever, the sql log still showed an error that it couldn't register the SPN. When I checked sys.dm_exec_connections, it showed NTLM authentication. So then our network admin manually registered the SPN for the X domain via these 2commands: setspn -A MSSQLSvc/servername.domain[b]X[/b]:port# Domain[b]X[/b]\SQLIDThat gave a little bit of progress. The sql log showed that it successfully registered the Service Principal Name!! It showed the instance name instead of the port number. Not sure if that matters, but I took that as a good sign.However, when I checked sys.dm_exec_connections using SSMS, it showed NTLM authentication. (sigh)We also ran the following: setspn -A MSSQLSvc/servername:port# Domain[b]X[/b]\SQLIDDidn't help.I don't know if a server restart is needed, but I did. That didn't help. Plus, the service didn't automatically restart. I didn't see any obvious entries in the event viewer logs.What else do I need to try to get it to use Kerberos? Are there Active Directory settings for the SQLID that need to change? Are there Kerberos settings that need to change? Is there something in the registry?Any help would be appreciated.Wed, 02 Mar 2011 10:28:53 GMTMarcia Qhttp://www.sqlservercentral.com/Forums/Topic1265876-1526-1.aspxHiIs it possible to have a username and log in specified in a connection string? I would like to create Membership Provider for .net that uses SQL's Roles and Users as it's datasource and every user will connect using there own username in the connection string. But is it possible at all to have only 1 log in for the SQL instance and all the users in the database use this login to authenticate? or is there a way for me to add extra information to the connection string and the SQL Auditing can use that data when auditing and that I can access that data from stored procedures.Thanks in advanced :-DTue, 13 Mar 2012 07:22:45 GMTBinaryDigithttp://www.sqlservercentral.com/Forums/Topic1268416-1526-1.aspxI have question:How do I prevent developers to change accounting data through existing stored procedure which I have already given permission to alter/exec. the stored procedure orignal code is nothing to do with accounting data, but if developers to alter the sp temperary change to code to update accounting data, they can. only solution I have is to seperate alter permission and Exec permission. developers could only have one permission. but we still want developers can access production server to their job on other tables and sps.thank you!Fri, 16 Mar 2012 11:41:39 GMTJinwen Tanghttp://www.sqlservercentral.com/Forums/Topic1268276-1526-1.aspxTwo local workgroups (no AD) in different geographic locations tied together via VPN. Need to setup replication from 'local' to 'dr' location. Yes, the ultimate answer includes 'it depends', but what does it depend on? Two types of replication are needed - one database is daily refresh, other will be hourly (every two hours?) transactional replication.1. Should we use Windows or SQL Server authentication?2. If 'dr' location's server is a copy of the virtual server at 'local', where are the landmines that will cause things to blow up (i.e., fail)?3. Should different login ids be used for each different replication - is this merely a question of style or are there good reasons to use different login ids?4. Currently, from 'local' can connect to 'dr' only by using the internal IP address. Other attempts result in either a '26' or a '40' error message, so something obvious is missing. What do I need to check?TIA!Fri, 16 Mar 2012 08:26:45 GMTsteve smith-401573http://www.sqlservercentral.com/Forums/Topic1268016-1526-1.aspxHi Team,We need to encrypt sensitive data after database has been restore....we already implement sql server inbuilt encryption function to encrypt data...but when restore happens we lost encryption or we need to re-build master key, certificate and encrypt data....etc...so, when restore happens which is the best solution to encrypt data (either we create SP to encrypt data / used in-build sql encryption function )?Thu, 15 Mar 2012 22:51:31 GMTSQL_Comphttp://www.sqlservercentral.com/Forums/Topic1263711-1526-1.aspxHi, i've recently been experimenting with encrypting connections to a SQL server (2008 R2) installation. I've not gone as far as creating SSL certificates and provisioning them on the server, i've simply made changes in the Config Manager to set FORCE ENCRYPTION and TRUST SERVER CERTIFICATE both to ON (or yes). The question I have is: to what level does this encrypt data coming out of the server. I compared packets (using WireShark) before and after, and they do "look" like the data has been encrypted but I wanted to hear from other people who know a lot more on the subject.Thanks in advanceNickThu, 08 Mar 2012 08:55:57 GMTNickBalaamhttp://www.sqlservercentral.com/Forums/Topic1251534-1526-1.aspxHi there. I'm wondering if there are any good ways to quickly time-lock an application that has been built at the database level. The scenario is:1. We want to deliver our software to a potential customer. Giving them 30 days to use it.2. After 30 days we've asked them to remove the application.However they may not... in which case I want a "back-door" time lock. I'm thinking of doing the following:We're looking for a specific logon user i.e. "Mickey" I would write a generic .NET CLM and compile it. It would store a hard coded date and the username.A logon trigger (we persist our sessions) would then validate against the CLM and either allow access or close the session (is that possible??)I'm wondering if anyone has had to do this quickly. It doesn't need to be 100% but the analogy would be "if they want to steal the car, lets at least not leave the keys in it". Thanks.Mon, 13 Feb 2012 14:35:25 GMTStevie T