Restricting Access to Underlying Tables in Different Databases While Allowing View Access
Hi Experts, I am looking to get assistance with the concept of ownership chaining in the SQL Server Database 2016. We have a data warehouse database that is maintained by an ETL Tool. We have segregated objects (e.g. Views, store procedure) from the data warehouse database to the another database on the same server. The non-data warehouse database which references to the Data warehouse database has very complex model of permissions and roles. I am unable to grant access to the objects mainly views in the non data warehouse database without granting select/View definition permissions on the data warehouse database. I'd like to know if it is possible to grant access to the views in the non-data warehouse database without granting direct access on the actual objects like Tables in the data warehouse database. Using windows Microsoft SQL Server 2019 (RTM-CU22-GDR)
Unable to verify Authenticode signatures due to error code 1627
Hi all, our SQL Server (which has been running just fine the past couple of years) suffered from a faulty (ECC) memory module last week, which is in the process of being replaced (currently the SQL Server runs on 96GB instead of 128GB. The SQL Server doubles as a Power BI Gateway btw, which might or might not be relevant. When rebooting the server (scheduled server reboot during the weekends), SQL Errorlog comes up with this error below, which I've never encountered before: "Unable to verify Authenticode signatures due to error code 1627. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support." I've tried to search for related problems and answers, however, so far I came up with nothing. I've read somewhere that Error code 1627 seems to suggest a system error, advising to reinstall the OS, but I'm not really convinced this is the same issue. Due to there having been a faulty memory module, I'm not entirely willing to write off the possibility, however, I do find this unlikely since the module was an ECC module. Does anyone know what causes this error or how to go about pinpointing the root cause? Any thoughts are appreciated. Cheers, NielsBrowser for SQL Server 2017 and 2019
Hello, my question is the following. Despite having the latest updates released for SQL Server 2019 and 2017, I notice that SQL Server Browser does not change versions, remaining in the versions mentioned below. I would like to know if they are in fact the latest versions of SQL Server Browser released in the 2017 and 2019 packages. If the versions below are correct, if they really have not been updated. Browser for SQL Server 2017 14.0.1000.169 Browser for SQL Server 2019 15.0.2000.5
Issue connecting to primary replica on AAG environment
Hello. I have an always on availability group environment with three replica: SQL01 (as primary), SQL02 (as secondary), SQL03 (as secondary), using Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64). I encounter an issue where i could not connect to SQL01 via SQL Server Management Studio (SSMS), then i did failover to SQL02 as primary and i tried again to connect to SQL01 and it successfully connected to SQL01. Upon the issue encounter, i made sure that: -i can remote the the three server of three SQL node -SQL server services on the three SQL are up -Cluster AAG is up (Failover Cluster Manager) -i could connect directly to SQL02 and SQL03, but not SQL01 Below are detail chronology: -11:55 AM: encountered error login database from application backend -12:00 PM: got log "A time-out occurred while waiting for buffer latch -- type 2, bp xxx, page xxx, stat xxx, database id: xxx, allocation unit Id: xxx, task xxx : 0, waittime 300 seconds, flags xxx, owning task xxx. Not continuing to wait." -12:01 PM: got log "Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group 'AAG' within the lease timeout period." "Always On Availability Groups connection with secondary database terminated for primary database 'xxx' on the availability replica 'SQL03' with Replica ID: {xxx}. This is an informational message only. No user action is required. "Always On Availability Groups connection with secondary database terminated for primary database 'xxx' on the availability replica 'SQL02' with Replica ID: {xxx}. This is an informational message only. No user action is required." After that, I tried to connect to SQL01 few times but failed (still failed until 30mins after the issue). Then I manually failed over to SQL02 as primary. Then I tried to connect to SQL01 again and succeed. Any idea on why i can't connect to SQL01 at first, but after doing failover to SQL02, i tried to connect to SQL01 again and succeed? Usually when the network disruptions happened between SQL nodes, after the connection is established between these nodes, i can connect to the SQL nodes. Thank you.
This is a question about the SQL Server 2019 trial version.
1. I understand that the trial version lasts 180 days. What happens after it expires? Can’t I use it at all?? I searched on the internet and it said that it will shut down within 1 hour when you turn it on. Is this correct? 2. I am using SQL server 2019 Enterprise Evaluation Edition, but the expiration date is not long. What I have is a standard license. Can I enter the standard key?
Prevent SQL Server from being able to execute PowerShell scripts.
Hi. Because of some security concerns, we are trying to disable ability of SQL Server to execute Powershell scripts. Does anyone have experience with this and what are the downsides (our SQL team is saying that they are not using that feature)? Thank you.Database Mail: (Syntax error, command unrecognized. The server response was: Authentication require)
We've got SQL Server in OCI. Configured Database Mail service with Basic authentication (SSL required) with right e-mail address, server name, login, etc from OCI mail service. But when I try to send test mail from SQL Server I get error: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2023-08-01T11:16:03). Exception Message: Cannot send mails to mail server. (Syntax error, command unrecognized. The server response was: Authentication required).) However, when I test send mail with curl, it works (from the same DB server): C:\Temp>curl -u ocid1.user.oc1..{user}:{password} smtp://{mail server} --mail-from test@test.example --mail-rcpt rcpt@test.example --ssl --upload-file C:/Temp/mail.txt Warning: --ssl is an insecure option, consider --ssl-reqd instead % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 80 0 0 100 80 0 374 --:--:-- --:--:-- --:--:-- 377 Version: Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor) What settings in SQL Server/Windows Server should I check? Thank you
Converting an SQL Server 2019 Ubuntu image to Alpine
Hi I want to host my SQL Server image on AWS Elastic Container Registry but want to make it smaller than this huge Ubuntu bloated image by converting the image to Alpine to save some dinero. It's more than 1GB right now on Ubuntu. I'm doing this on Powershell, windows 10. The problem is https://github.com/microsoft/mssql-docker/issues/405. And yes I tried using https://hub.docker.com/_/microsoft-mssql-tools and had no success. I will probably have to end up using Ubuntu but if anyone knows how to use Alpine for this use case then please let me know what I can do. Basically, if I run the container I built it doesn't start. So I first run this command to build the image. docker build -t img_pitcher8_alpine . Then this command to create the container: docker run -d -p 1440:1444 --name contai_pitcher8_sql_server_alpine img_pitcher8_alpine >> 033e377f8c101155afa1e4ef42ea1583365133237e414d30b6a770e1b7b08790 I cannot find any logs for the container in Docker Desktop if I run: docker logs contai_pitcher8_sql_server_alpine If it helps this is the docker file with the password omitted. # Use mcr.microsoft.com/mssql/server:2019-latest as the base image FROM mcr.microsoft.com/mssql/server:2019-latest as base # Set environment variables ENV ACCEPT_EULA=Y ENV SA_PASSWORD=***** ENV MSSQL_PID=Developer # Copy database files into image COPY ./Pitcher8.mdf /var/opt/mssql/data/Pitcher8.mdf COPY ./Pitcher8_log.ldf /var/opt/mssql/data/Pitcher8_log.ldf # Start a new stage with Alpine Linux as the base image FROM alpine # Install necessary dependencies RUN apk add --no-cache openssl libintl icu # Copy files from base into current image COPY --from=base /opt/microsoft/ /opt/microsoft/ COPY --from=base /opt/mssql-tools/ /opt/mssql-tools/ # Set up volume for database data VOLUME /var/opt/mssql/data # Expose port 1444 inside the container EXPOSE 1444 I bound it to 1444 as I already have a container on port 1433 and want to avoid conflicts.Blocking due to recompile locks
I am seeing a lot of blocking with wait resource as OBJECT: n:zzz:0 [COMPILE] and TAB: n:zzz:0 [COMPILE] with a lot of RESOURCE_SEMAPHORE_QUERY_COMPILE waitstypes. This is causing a lot of CPU overhead and it is almost reaching close to 100% most of the time. We are running with Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) Min Server Memory 51200 MB Max Server Memory 112860 MB Processor Intel(R) Xeon(R) Platinum 8259CL CPU @ 2.50GHz, 2500 Mhz, 8 Core(s), 16 Logical Processor(s) RAM 128 GB MAXDOP 1 CTP 5 Compatibility 130 LCE ON The same combination test when run on similar SQL 2016 server with identical configuration is running without any issue. Can anyone share anything on this as what could be the probable cause?